将MySQL datetime分组到不同的时区 [英] Grouping MySQL datetime into intervals irrespective of timezone
问题描述
此问题已被问到之前的但我面临着一个稍微不同的问题。
This question has been asked before but I am facing a slightly different problem.
我有一个表来记录事件并存储他们的时间戳(如datetime)。我需要能够将时间分解成块,并获得在该间隔内发生的事件数。间隔可以是自定义的(从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将其除以秒数间隔,取其底层函数并将其乘以秒数。最后将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.
这个小时间间隔可以正常工作。
This works fine for small 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;
这给出了正确的输出
+---------------------+-------+
| 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。分割和使用楼层将会将分钟部分设置为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
EDIT2: 您必须在最小参数日期中通过的语句中的区域数,可以使用:
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
只需将最小datetime参数传入一次连接子选择。
And simply pass in your minimum datetime parameter once into the join subselect.
您甚至可以在连接子选择中为秒间隔设置第二列(例如 3600
),并将其命名列如 secinterval
...然后将< n>
更改为 b .secinterval
,所以你只需要传递你的最小日期参数AND间隔一次。
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.
这篇关于将MySQL datetime分组到不同的时区的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!