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
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
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)