CODEWITHSUNDEEP
mysqlsqldatetimetime
rabudde
rabudde

Reputation: 7722

Time calculation (round time to next time slot)

For doing some statistics on my incoming data on webserver I do some ETL work on this data. So I aggregate some thing based on quarter hours. I.e.:

inserted  | tstamp
8:00        8:00
8:09        8:00
8:16        8:15
8:29        8:15
8:42        8:30
8:45        8:45
8:59        8:45
9:01        9:00

The columns above are datetime column, which also contain a date (I left it out only for demonstration purposes). The job runs every 3 minutes via cron and takes only data from last 3 minutes. Now, when the cron jobs crash while working, there is the risk, that a time slot contains wrong data when the jobs restarts next time. So I want the job to begin at last slot. I.e. when the job runs at 08:24 last and crash it should start again at 08:15.

My question is now, is there a simple MySQL statement which gives me the last time slot before the actual time?

The statement I'm using in ETL to determine actual time slot is

CONCAT(
    DATE_FORMAT(a.inserted, '%Y-%m-%d %H:'),
    IF(MINUTE(a.inserted)<15,'00',
        IF(MINUTE(a.inserted)<30,'15',
        IF(MINUTE(a.inserted)<45,'30',
        '45'))
    ),
    ':00'
) AS tstamp

Could this be done in a more elegant way?

Upvotes: 2

Views: 597

Answers (3)

Bohemian
Bohemian

Reputation: 425043

Just use FLOOR() to round minutes down to multiples of 15:

CONCAT(DATE_FORMAT(a.inserted, '%Y-%m-%d %H:'), 
    15 * FLOOR(MINUTE(a.inserted) / 15)), ':00') AS tstamp

or use FLOOR() on the time and format that:

DATE_FORMAT(FROM_UNIXTIME(900 * FLOOR(UNIX_TIMESTAMP(a.inserted) / 900)), '%Y-%m-%d %H:%M:%s') as tstamp

(900 is the number of seconds in 15 minutes)

Upvotes: 1

O. Jones
O. Jones

Reputation: 108676

You are very close. How about this?

  SELECT DATE_FORMAT( a.inserted '%Y-%m-%d %H:00:00') +
     INTERVAL (MINUTE(a.inserted) - MINUTE(a.inserted)%15) MINUTE

Upvotes: 1

Andomar
Andomar

Reputation: 238086

You can use unix_timestamp() to get the current time in number of seconds since 1970. The last 3-minute interval is that time modulo 3*60 seconds:

select  from_unixtime(unix_timestamp() - mod(unix_timestamp(), 180))
            as StartOfInterval
,       from_unixtime(unix_timestamp() - mod(unix_timestamp(), 180)-180) 
            as StartOfLastInterval

Live example at SQL Fiddle.

Upvotes: 2

Related Questions