php - 如何处理 PHP + MySQL timezones 和 MySQL GROUP BY [date] 查询？

Question

我正在使用 Drupal 7、PHP 和 MySQL，并试图在从 MySQL 数据库查询时解决时区问题。

Drupal 从一开始就很好地处理日期:所有日期都存储为数据库中的 UTC 时间戳 int，并且时区转换通过每个用户配置文件中的时区设置在每个用户的基础上发生，使用 PHP 5 的内置时区功能(因此每次运行 PHP 脚本时，脚本的时区都设置​​为当前用户的时区)。

所以只要我们坚持使用 PHP，这一切都很好，花花公子，而且相当轻松。

当我们引入 MySQL 时，事情开始变得棘手，因为似乎没有办法将当前 PHP 脚本时区与给定的 MySQL 查询完美同步。似乎最佳实践要求在 PHP 中处理所有时区转换:只查询数据库的原始时间戳，然后根据需要在 PHP 中进行转换。

这在大多数情况下似乎是合理的(即使有时会慢一点)，但是我应该如何处理 MySQL GROUP BY [date] 查询？例如，我正在构建一个模块来处理分析，并且经常想要执行以下操作:

GROUP BY YEAR(FROM_UNIXTIME(u.created)), MONTH(FROM_UNIXTIME(u.created))

所以我们遇到了时区问题......

想到的可能的解决方案:

1. 对时区进行硬编码:在我的模块中使用 date_default_timezone_set() 以确保 PHP 时区始终设置为系统时区(因此 MySQL 时区 = PHP 时区)。换句话说，分析时区将被硬编码，而不是尊重查看分析的用户的时区。这确实不理想，因为我们希望多个时区的用户能够使用他们的时区访问分析。此外，date_default_timezone_set() 似乎将 Drupal 搞得一团糟，因为它为整个脚本设置了时区，而不仅仅是在特定函数中...

2. 忘记在查询中使用 GROUP BY:只需从数据库中获取所有原始数据(有数万或数十万行)，然后在 php 中使用 for 循环按日期对结果进行分组...这个解决方案似乎会占用更多资源，速度更慢，而且有些荒谬。

所以我想我想问的是，我错过了什么吗？这里有我不知道的最佳实践吗？

非常感谢您的帮助!

Answer 1

我会考虑这样的方法

SET time_zone = '+02:00';

http://dev.mysql.com/doc/refman/5.5/en/time-zone-support.html

还有

GROUP BY FROM_UNIXTIME(u.created, '%Y-%m');

由于 FROM_UNIXTIME 的时间基于 time_zone，因此这应该会产生预期的结果。

要撤消之后的time_zone 更改，请考虑保存 SELECT TIMEDIFF(NOW(), CONVERT_TZ(now(), @@session.time_zone, '+00:00'));首先，然后将其设置为保存的值。