将 MySQL 日期时间分组为间隔，而不考虑时区

Grouping MySQL datetime into intervals irrespective of timezone(将 MySQL 日期时间分组为间隔，而不考虑时区)

问题描述

此问题已在之前提出 但我面临的问题略有不同.

This question has been asked before but I am facing a slightly different problem.

我有一个记录事件并存储它们的时间戳(作为日期时间)的表.我需要能够将时间分成几块并获取在该时间间隔内发生的事件数.间隔时间可以自定义(比如从 5 分钟到 1 小时甚至更长).

I have a table which logs events and stores their timestamps (as datetime). I need to be able to break up time into chunks and get number of events that occurred in that interval. The interval can be custom (Say from 5 minutes to 1 hour and even beyond).

显而易见的解决方案是将 datetime 转换为 unix_timestamp 将其除以间隔中的秒数，取其 floor 函数并将其乘回秒数.最后将 unix_timestamp 转换回 datetime 格式.

The obvious solution is to convert the datetime to unix_timestamp divide it by number of seconds in the interval, take its floor function and multiply it back by the number of seconds. Finally convert the unix_timestamp back to the datetime format.

这适用于小间隔.

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;

这给出了正确的输出

+---------------------+-------+
| 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~~~~~~~~~~~~

但如果我将间隔增加到 1 小时(3600 秒)

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

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 |
+---------------------+-------+

据我所知，未正确设置边界的原因是 unix_timestamp 会将时间从我的本地时区 (GMT + 0530) 转换为 UTC，然后输出数值.

The reason, as far as I could gauge, for the boundaries not being set properly is that unix_timestamp will convert time from my local timezone (GMT + 0530) to UTC and then output the numerical value.

所以像 2012-08-03 00:00:00 这样的值实际上是 2012-08-02 18:30:00.划分和使用 floor 会将分钟部分设置为 00.但是当我使用 from_unixtime 时，它​​会将其转换回 GMT + 0530，因此给我从 30 分钟开始的间隔.

So a value like 2012-08-03 00:00:00 will actually be 2012-08-02 18:30:00. Dividing and using floor will set the minutes part to 00. But when I use from_unixtime, it will convert it back to GMT + 0530 and hence give me intervals that begin at 30 mins.

无论时区如何，如何确保查询正常工作?我使用 MySQL 5.1.52，所以 to_seconds() 不可用

How do I ensure the query works correctly irrespective of the timezone? I use MySQL 5.1.52 so to_seconds() is not available

无论时间间隔如何(可以是小时、分钟、天)，查询也应该正确触发.一个通用的解决方案将不胜感激

The query should also fire correctly irrespective of the interval (can be hours, minutes, days). A generic solution would be appreciated

推荐答案

你可以使用TIMESTAMPDIFF 按时间间隔分组:

You can use TIMESTAMPDIFF to group by intervals of time:

对于指定的小时间隔，您可以使用:

For a specified 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

将 2012-08-03 00:00:00 的出现替换为您的最小输入日期.

Replace the occurances of 2012-08-03 00:00:00 with your minimum input date.

<n> 是您指定的间隔，以 小时 为单位(每 2 小时、3 小时等.)，你可以在几分钟内做同样的事情:

<n> is your specified interval in hours (every 2 hours, 3 hours, etc.), and you can do the same for 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

<n> 是您指定的时间间隔，以 分钟 为单位(每 45 分钟，90 分钟，等).

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

确保您将最小输入日期(在本例中为 2012-08-03 00:00:00)作为第二个参数传递给 TIMESTAMPDIFF.

Be sure you're passing in your minimum input date (in this example 2012-08-03 00:00:00) as the second parameter to TIMESTAMPDIFF.

如果您不想担心在 TIMESTAMPDIFF 函数中选择哪个间隔单位，那么当然只需以秒为单位的间隔(300 = 5 分钟、3600 = 1 小时、7200 = 2 小时等)

If you don't want to worry about which interval unit to pick in the TIMESTAMPDIFF function, then of course just do the interval by 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

要解决您关于减少语句中必须传递最小参数日期的区域数量的评论，您可以使用:

To address your comment pertaining to reducing the number of areas in the statement where you have to pass in 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 simply pass in your minimum datetime parameter once into the join subselect.

您甚至可以在连接子选择中为您的秒间隔创建第二列(例如 3600)并将该列命名为 secinterval...然后更改 <n>'s to b.secinterval，所以你只需要传入你的最小日期参数和间隔一次.

You can even make a second column in the join subselect for your seconds interval (e.g. 3600) and name the column something like secinterval... then change the <n>'s to b.secinterval, so you only have to pass in your minimum date parameter AND interval one time each.

SQLFiddle 演示

这篇关于将 MySQL 日期时间分组为间隔，而不考虑时区的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！