CODEWITHSUNDEEP
mysqltimestampunix-timestampsampling
Peter H
Peter H

Reputation: 901

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



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?

Upvotes: 0

Views: 60

Answers (1)

Strawberry
Strawberry

Reputation: 33935

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.

Upvotes: 1

Related Questions