按小时对结果进行分组的MySQL查询，并考虑了夏令时

Question

我有一个使用sql查询的报告，如下所示：

SELECT HOUR(somedatetime) AS hour, COUNT(*) AS count
FROM table
GROUP BY HOUR(surveyenddatetime)

我在这里简化了它，省略了where子句，该子句可以将结果限制在给定的时间段内。它可以很好地将所选时间段内输入的总记录按一天中的小时分组。

然而，它有一个致命的缺陷。如果选定的时间段包括与运行报告的用户不在同一“时区”的记录，例如夏令时之前的记录和夏令时之后的记录，则此操作将不起作用。所有记录都存储在GMT/UTC on的datetime字段中，并通过web前端转换为php中的本地时间。但是这个查询是在这一切发生之前运行的，php无法知道每个小时的总计的原始日期是什么。

有没有一种方法可以解决这个查询，只使用一个MySQL查询就可以考虑夏令时，或者我必须做一些更复杂的事情？

Answer 1

CONVERT_TZ

SELECT 
    HOUR(CONVERT_TZ(`somedatetime`, 'UTC', 'Europe/Amsterdam')) AS `hour`, 
    COUNT(*) AS `count`
FROM table
GROUP BY `hour`

将用户的时区传递到查询中，而不是“Europe/Amsterdam”。

另请参阅：https://dev.mysql.com/doc/refman/5.5/en/mysql-tzinfo-to-sql.html (您可能需要使用时区数据初始化MySQL。)