MySQL break down start and end timestamp into X number of samples

Question

I'm after a method to dynamically create a list of time values between two dates in MySQL. For example, a user would enter in a start and end timestamp.

E.G Start Date = 2014/01/01 00:00:00
End Date = 2014/01/02 00:00:00
The query would then work it's "magic" and return list of time values with all the 5 minute samples between the start and end date.

1/01/2014 0:00
1/01/2014 0:05
1/01/2014 0:10
1/01/2014 0:15
1/01/2014 0:20
1/01/2014 0:25
.......................
.......................
1/01/2014 23:30
1/01/2014 23:35
1/01/2014 23:40
1/01/2014 23:45
1/01/2014 23:50
1/01/2014 23:55

Does anyone have any pointers on how I should try and accomplish this?

I have samples of readings in another table, which I average over 5 minute intervals. The problem is that I may be missing data for certain time periods so my goal was to join the results of a query above (if I can) to the other table so I can identify where data is missing. — Peter H, Nov 29 '14 at 09:12

score 1 · Answer 1 · answered Nov 29 '14 at 11:57

So my thinking about this problem would start here...

 SELECT UNIX_TIMESTAMP(NOW()) ut
      , ROUND(UNIX_TIMESTAMP(NOW())/300,0) ut_rounded
      , FROM_UNIXTIME(ROUND(UNIX_TIMESTAMP(NOW())/300,0)*300) now_rounded;
 +------------+------------+---------------------+
 | ut         | ut_rounded | now_rounded         |
 +------------+------------+---------------------+
 | 1417261935 |    4724206 | 2014-11-29 11:50:00 |
 +------------+------------+---------------------+

...and a few seconds (and up to five minutes) later...

 SELECT UNIX_TIMESTAMP(NOW()) ut
      , ROUND(UNIX_TIMESTAMP(NOW())/300,0) ut_rounded
      , FROM_UNIXTIME(ROUND(UNIX_TIMESTAMP(NOW())/300,0)*300) now_rounded;
 +------------+------------+---------------------+
 | ut         | ut_rounded | now_rounded         |
 +------------+------------+---------------------+
 | 1417261951 |    4724207 | 2014-11-29 11:55:00 |
 +------------+------------+---------------------+

So every 5 minutes ut_rounded increases by 1, so now we're just looking for gaps in a sequence - a problem for which patterns already exist.

MySQL break down start and end timestamp into X number of samples

1 Answers1