如何按日期分组，考虑时区和夏令时

Question

我的数据库中存储了一堆事件。每个事件都有一个开始时间，以 UTC 格式存储为DATETIME。每个用户都可以选择自己的时区。

我想显示一个日历，它只显示每月每一天的事件数量。我无法GROUP BY DATE(start_dt)，因为用户的一天[不一定]从 00：00：00 UTC 开始。此外，用户的时区可能在月中有一个 DST 开关。

那么我唯一的选择是获取给定月份的每个事件，然后使用我的服务器端语言对它们进行分组和计数吗？

Answer 1

你可以

试试

SELECT ... FROM ... GROUP BY DATE(CONVERT_TZ(start_dt,'UTC','America/Vancouver'))

请注意，您需要将时区数据加载到 MySQL 中，然后才能正常工作。

Answer 2

不幸的是，DATETIME列的存储没有时区信息：

当前时区设置不会影响函数（如 UTC_TIMESTAMP（） ））显示的值或"日期"、"时间"或"日期时间"列中的值。这些数据类型中的值也不以 UTC 格式存储;时区仅在从 TIMESTAMP 值转换时适用于它们。如果需要 DATE、TIME 或 DATETIME 值的区域设置特定算术，请将其转换为 UTC，执行算术运算，然后转换回来。[MySQL 文档]

幸运的是，这确实意味着日期/时间函数应该与时区支持一起使用，但仅适用于TIMESTAMP列。进入TIMESTAMP列后，日期/时间函数应该能够考虑@@session.time_zone，您可以根据需要进行更改以获取不同的时区。

因此，您的选择是保留包含DATETIME列的表，并自己进行所有分组，或者将这些列转换为TIMESTAMP并让MySQL执行此操作 - 但是如果列已经处于不同且不一致的时区，那么您的工作将为您削减...

Answer 3

您可以

中间切换到用户 tz：

  SET @@session.time_zone = "+05:00"; 
  select ... from .. group by MONTH(datefield);
  SET @@session.time_zone = @@global.time_zone;

刚刚发现： 如何设置 MySQL 的时区？