How to select 24 hours record in mysql each of one hour?

Question

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

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Answer 1

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)

How to select 24 hours record in mysql each of one hour?

Sign up