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



Answers

Just convert to timestamp and add 86400 which is equivalent to 1 day

  select * from table where  UNIX_TIMESTAMP(`Starttime`) >= UNIX_TIMESTAMP('datetimeStart') and UNIX_TIMESTAMP(`Endtime`) <= UNIX_TIMESTAMP('datetimeStart')+86400


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)
By : Ponnarasu


There may be a chance of string value in the column RVU.MA. Try out with the below query to find out such descrepancies.

   SELECT *
   FROM RVU
   WHERE ISNUMERIC (RVU.MA)!=1


This video can help you solving your question :)
By: admin