javascript - 检索夏令时期间添加的 UTC 时间

Question

我有一个用户可以在其中存储和事件的表。事件的开始时间存储在一列中，列字段为 time 。在夏令时期间，我所在的地方，小时偏移量为 -5因此，如果用户存储从 12:00pm 开始的事件它将在数据库中存储为 17:00:00 。如果用户要在非夏令时创建具有完全相同开始时间的事件，则时区偏移将为 -4它将在数据库中存储为 16:00:00 .

如果用户选择从中午 12:00 开始的所有事件，并且是夏令时，我将使用 javascript 获取用户时区偏移量(在本例中为 -5)并相应地创建查询 WHERE startTime > 17:00:00 。问题是在非夏令时创建的所有事件的开始时间均为 16:00:00因为偏移量是 -4而不是-5所以他们不会被选中。

这个问题有解决办法吗？我存储事件开始时间的方法完全错误吗？

Answer 1

如果您有一个 DATE 和一个 TIME 值，并且您知道记录这些日期和时间时使用的地理时区，则可以使用 CONVERT_TZ()从本地时间数据中检索 UTC 时间。

首先，使用 ADDTIME() 将 DATE 和 TIME 数据组合成 DATETIME 值。这将为您提供 5 月 5 日 07:00。

 ADDTIME(DATE('2015-05-01'), TIME('07:00:00'))

然后将其从本地地理时间转换为 UTC。

CONVERT_TZ( ADDTIME(DATE('2015-05-01'), TIME('07:00:00')), 'America/New_York', 'UTC')

尝试此 MySQL 语句，您将看到 America/New_York 转换正确处理夏令时。

select CONVERT_TZ( ADDTIME(DATE('2015-05-01'), TIME('07:00:00')), 'America/New_York', 'UTC'),
       CONVERT_TZ( ADDTIME(DATE('2015-01-01'), TIME('07:00:00')), 'America/New_York', 'UTC')

这适用于各种时区。例如，参见 here ，查看列表。

如果您从头开始进行数据库设计，那么在同一时区记录所有时间是明智之举。事实上，将您的服务器设置为以 UTC(而不是本地时间)运行。执行此操作的简单方法是使用 MySQL 的 TIMESTAMP 数据类型:它始终以 UTC 内部记录时间。它在记录时从本地时间转换，并在检索时返回本地时间。它使用连接的时区设置来执行此操作。

编辑

假设您有一个包含 user_id、evdate、evtime 和描述列的事件表。假设您还有一个包含 user_id 和 user_tz 列的用户表。然后，您可以使用如下查询查找两个日期 utc 之间的事件。

  SELECT u.user_name, e.description, 
         CONVERT_TZ( ADDTIME(e.evdate, e.evtime), u.user_tz, 'UTC') start
    FROM event.e
    JOIN user.u ON e.user_id = u.user_id
   WHERE CONVERT_TZ( ADDTIME(e.evdate, e.evtime), u.user_tz, 'UTC') >= '2015-03-01 00:00:00'

这假设每个用户都有一个指定的时区，并且该用户的所有日期和时间都存储在该用户指定的时区中。