Grouping MySQL datetime into intervals regardless of time zone

Question

This question has been asked before , but I ran into a slightly different problem.

I have a table that logs events and stores their timestamps (like date and time). I need to be able to break the time into pieces and get the number of events that occurred in this interval. The interval can be normal (say from 5 minutes to 1 hour or even higher).

The obvious solution is to convert datetime to unix_timestamp, divide it by the number of seconds in intervals, execute its gender function and multiply it by the number of seconds. Finally, convert unix_timestamp to datetime format.

This works great for short intervals.

select from_unixtime(floor(unix_timestamp(event.timestamp)/300)*300) as start_time, count(*) as total from event where timestamp>='2012-08-03 00:00:00' group by start_time; 

This gives the correct conclusion.

 +---------------------+-------+ | start_time | total | +---------------------+-------+ | 2012-08-03 00:00:00 | 11 | | 2012-08-03 00:05:00 | 4 | | 2012-08-03 00:10:00 | 4 | | 2012-08-03 00:15:00 | 7 | | 2012-08-03 00:20:00 | 8 | | 2012-08-03 00:25:00 | 1 | | 2012-08-03 00:30:00 | 1 | | 2012-08-03 00:35:00 | 3 | | 2012-08-03 00:40:00 | 3 | | 2012-08-03 00:45:00 | 5 | ~~~~~OUTPUT SNIPPED~~~~~~~~~~~~ 

But if I increase the interval to 1 hour (3600 seconds)

 mysql> select from_unixtime(floor(unix_timestamp(event.timestamp)/3600)*3600) as start_time, count(*) as total from event where timestamp>='2012-08-03 00:00:00' group by start_time; +---------------------+-------+ | start_time | total | +---------------------+-------+ | 2012-08-02 23:30:00 | 35 | | 2012-08-03 00:30:00 | 30 | | 2012-08-03 01:30:00 | 12 | | 2012-08-03 02:30:00 | 18 | | 2012-08-03 03:30:00 | 12 | | 2012-08-03 04:30:00 | 4 | | 2012-08-03 05:30:00 | 3 | | 2012-08-03 06:30:00 | 13 | | 2012-08-03 07:30:00 | 269 | | 2012-08-03 08:30:00 | 681 | | 2012-08-03 09:30:00 | 1523 | | 2012-08-03 10:30:00 | 911 | +---------------------+-------+ 

The reason I can appreciate that boundaries that are not set properly is that unix_timestamp converts the time from my local time zone (GMT + 0530) to UTC and then outputs a numeric value.

So, a value like 2012-08-03 00:00:00 will actually be 2012-08-02 18:30:00. Splitting and using the floor will set the minute part to 00. But when I use from_unixtime, it converts it back to GMT + 0530 and therefore gives me intervals that start after 30 minutes.

How to ensure the correct execution of the request, regardless of time zone? I am using MySQL 5.1.52, so to_seconds () is not available

EDIT: The request should also work correctly regardless of the interval (there may be hours, minutes, days). An overall solution would be appreciated.

Answer 1

You can use TIMESTAMPDIFF to group by time intervals:

For a given interval of hours, you can use:

 SELECT '2012-08-03 00:00:00' + INTERVAL FLOOR(TIMESTAMPDIFF(HOUR, '2012-08-03 00:00:00', timestamp) / <n>) * <n> HOUR AS start_time, COUNT(*) AS total FROM event WHERE timestamp >= '2012-08-03 00:00:00' GROUP BY start_time 

Replace events 2012-08-03 00:00:00 with the minimum entry date.

<n> is your specified interval in hours (every 2 hours, 3 hours, etc.), and you can do the same in minutes:

 SELECT '2012-08-03 00:00:00' + INTERVAL FLOOR(TIMESTAMPDIFF(MINUTE, '2012-08-03 00:00:00', timestamp) / <n>) * <n> MINUTE AS start_time, COUNT(*) AS total FROM event WHERE timestamp >= '2012-08-03 00:00:00' GROUP BY start_time 

Where <n> is your specified interval in minutes (every 45 minutes, 90 minutes, etc.).

Make sure that you pass the minimum input date (in this example 2012-08-03 00:00:00 ) as the second parameter TIMESTAMPDIFF .

---

EDIT: If you do not want to worry about which intermediate block to select in the TIMESTAMPDIFF function, then of course just do the interval in seconds (300 = 5 minutes, 3600 = 1 hour, 7200 = 2 hours, etc.)

 SELECT '2012-08-03 00:00:00' + INTERVAL FLOOR(TIMESTAMPDIFF(SECOND, '2012-08-03 00:00:00', timestamp) / <n>) * <n> SECOND AS start_time, COUNT(*) AS total FROM event WHERE timestamp >= '2012-08-03 00:00:00' GROUP BY start_time 

EDIT2: To respond to your comment regarding reducing the number of areas in an instruction in which you must pass on your minimum parameter date, you can use:

 SELECT b.mindate + INTERVAL FLOOR(TIMESTAMPDIFF(SECOND, b.mindate, timestamp) / <n>) * <n> SECOND AS start_time, COUNT(*) AS total FROM event JOIN (SELECT '2012-08-03 00:00:00' AS mindate) b ON timestamp >= b.mindate GROUP BY start_time 

And just pass your minimum datetime parameter once per subsect. connections.

You can even make the second column in the join subselect for the interval of seconds (e.g. 3600 ) and name the column something like secinterval ... then change <n> to b.secinterval so that you only have to go through your minimum date parameter and interval one every time.

---

SQLFiddle Demo

Answer 2

simpler way:

Method1

 select date(timestamp) as date_timestamp, hour(timestamp) as hour_timestamp, count(*) as total from event where timestamp>='2012-08-03 00:00:00' group by date_timestamp, hour_timestamp 

if you want to use the original approach.

Method 2

 select from_unixtime(floor(unix_timestamp(event.timestamp-1800)/3600)*3600+1800) as start_time, count(*) as total from event where timestamp>='2012-08-03 00:00:00' group by start_time; 

EDIT1

for the first method, it also allows the user to set different intervals. For example, if a user wants the log to be grouped for 15 minutes,

 select date(time) as date_timestamp, hour(time) as hour_timestamp, floor(minute(time) as minute_timestamp / 15) * 15 as minute_timestamp count(*) as total from event group by date_timestamp, hour_timestamp, minute_timestamp