Hemant Sangle Hemant Sangle - 2 months ago 5
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)
Comments