MySQL Question
How to select 24 hours record in mysql each of one hour?
Input to the query is date and start time
and end time will be same as the start time
So i want the 24 hours record by the given time
For Example
date=2016-08-11
Start time=06:30:00 AM
so the end time is also 06:30:00 AM
but the date should be changed to the 2016-08-12
dynamically and end time should be 06:30:00 AM
and the interval should be 1 hour
Expected Result
Date Starttime Endtime
2016-08-11 00:00:07 00:21:55
2016-08-11 01:00:27 01:59:10
2016-08-11 02:00:09 02:59:33
table structure as below
Date_ Starttime Endtime
'2016-08-11' 00:00:07 00:21:55
'2016-08-11' 01:00:27 01:29:32
'2016-08-11' 01:30:32 01:59:10
'2016-08-11' 02:00:09 02:29:49
'2016-08-11' 02:30:48 02:59:33
'2016-08-11' 03:30:31 03:59:13
'2016-08-11' 04:00:12 04:29:47
'2016-08-11' 04:30:46 04:59:40
'2016-08-11' 05:00:39 05:29:42
'2016-08-11' 05:30:41 05:59:24
'2016-08-11' 06:00:23 06:29:01
'2016-08-11' 06:30:00 06:59:33
'2016-08-11' 07:00:31 07:29:25
'2016-08-11' 07:30:24 07:59:57
'2016-08-11' 08:00:57 08:29:39
'2016-08-11' 08:30:38 08:59:58
'2016-08-11' 09:00:50 09:29:22
'2016-08-11' 09:30:14 09:59:17
'2016-08-11' 10:00:08 10:29:58
'2016-08-11' 10:30:48 10:59:57
'2016-08-11' 11:00:48 11:29:31
'2016-08-11' 11:30:26 11:59:32
'2016-08-11' 12:00:24 12:29:35
for any explation please comment
Answer
This may help you
SELECT date,MIN(Starttime),MAX(Endtime) FROM your_table
WHERE CONCAT(date,' ',Starttime) >= CONCAT(?,' ',?)
AND CONCAT(date,' ',Starttime) < CONCAT(DATE_ADD(?,INTERVAL 1 DAY),' ',?)
GROUP BY date,HOUR(Starttime)
Give your input for '?' like below.
SELECT date,MIN(Starttime),MAX(Endtime) FROM your_table
WHERE CONCAT(date,' ',Starttime) >= CONCAT('2016-08-11',' ','06:30:00')
AND CONCAT(date,' ',Starttime) < CONCAT(DATE_ADD('2016-08-11',INTERVAL 1 DAY),' 06:30:00')
GROUP BY date,HOUR(Starttime)
Source (Stackoverflow)