MySQL日期时间字段和夏令时 - 如何引用“额外”小时?

2022-08-30 07:40:17

我使用的是美国/纽约时区。在秋天,我们“后退”一个小时 - 实际上在凌晨2点“增加”一个小时。在过渡点,将发生以下情况:

它是01:59:00 -04:00
,然后1分钟后它变成:
01:00:00 -05:00

因此,如果你只是简单地说“凌晨1:30”,那么你指的是第一次1:30还是第二次,这是模棱两可的。我正在尝试将调度数据保存到MySQL数据库,但无法确定如何正确保存时间。

问题是:
“2009-11-01 00:30:00”在内部存储为 2009-11-01 00:30:00 -04:00
“2009-11-01 01:30:00”在内部存储为 2009-11-01 01:30:00 -05:00

这很好,相当预期。但是,如何将任何内容保存到 01:30:00 -04:00?文档没有显示对指定偏移量的任何支持,因此,当我尝试指定偏移量时,它被适当地忽略了。

我想到的唯一解决方案是将服务器设置为不使用夏令时的时区,并在我的脚本中进行必要的转换(我使用PHP)。但这似乎没有必要。

非常感谢您的任何建议。


答案 1

我已经弄清楚了它,以达到我的目的。我将总结我所学到的东西(抱歉,这些笔记很详细;它们对我未来的推荐和其他任何东西一样多)。

与我之前的评论中所说的相反,DATETIME 和 TIMESTAMP 字段的行为确实不同。TIMESTAMP 字段(如文档所示)采用您以“YYYY-MM-DD hh:mm:ss”格式发送的任何内容,并将其从当前时区转换为 UTC 时间。每当您检索数据时,反之则以透明方式发生。日期时间字段不进行此转换。他们拿走你寄给他们的任何东西,直接存储。

DATETIME 和 TIMESTAMP 字段类型都不能准确地将数据存储在遵守 DST 的时区中。如果存储“2009-11-01 01:30:00”,则无法区分所需的 1:30am 版本 -- -04:00 或 -05:00 版本。

好的,因此我们必须将数据存储在非DST时区(例如UTC)。TIMESTAMP 字段无法准确处理此数据,原因我将解释:如果您的系统设置为 DST 时区,那么您放入 TIMESTAMP 的内容可能不是您返回的内容。即使您向其发送已转换为 UTC 的数据,它仍将假定数据位于您的本地时区,并再次转换为 UTC。当您的本地时区遵守 DST 时,此 TIMESTAMP 强制执行的本地到 UTC 回传到本地的往返是有损的(因为“2009-11-01 01:30:00”映射到 2 个不同的可能时间)。

使用DATETIME,您可以将数据存储在所需的任何时区,并确信您将收到任何发送的数据(您不会被迫进入TIMESTAMP字段强加给您的有损往返转换)。因此,解决方案是使用DATETIME字段,并在保存到该字段之前,将系统时区转换为要保存的任何非DST区域(我认为UTC可能是最佳选择)。这允许您将转换逻辑构建到脚本语言中,以便您可以显式保存等同于“2009-11-01 01:30:00 -04:00”或“”2009-11-01 01:30:00 -05:00“的UTC等效项。

另一个需要注意的重要事情是,如果您将日期存储在DST TZ中,MySQL的日期/时间数学函数将无法在DST边界周围正常工作。因此,更有理由在UTC中保存。

简而言之,我现在这样做:

从数据库中检索数据时:

在MySQL之外将数据库中的数据显式解释为UTC,以获得准确的Unix时间戳。为此,我使用PHP的strtotime()函数或其DateTime类。使用MySQL的CONVERT_TZ()或UNIX_TIMESTAMP()函数无法在MySQL内部可靠地完成此操作,因为CONVERT_TZ只会输出一个“YYYY-MM-DD hh:mm:ss”值,该值存在歧义问题,并且UNIX_TIMESTAMP()假设其输入在系统时区,而不是数据实际存储的时区(UTC)。

将数据存储到数据库时:

将您的日期转换为您在MySQL之外所需的精确UTC时间。例如:使用 PHP 的 DateTime 类,您可以指定“2009-11-01 1:30:00 EST”,与“2009-11-01 1:30:00 EDT”不同,然后将其转换为 UTC 并将正确的 UTC 时间保存到 DATETIME 字段中。

唷。非常感谢大家的投入和帮助。希望这能为其他人节省一些麻烦。

顺便说一句,我在MySQL 5.0.22和5.0.27上看到了这个


答案 2

坦率地说,MySQL的日期类型是破碎的,无法正确存储所有时间,除非您的系统设置为恒定的偏移时区,如UTC或GMT-5。(我使用的是MySQL 5.0.45)

这是因为您无法在夏令时结束前一小时内存储任何时间。无论您如何输入日期,每个日期函数都会将这些时间视为在切换后的一小时内。

我的系统的时区是 。让我们尝试存储1257051600(2009 年 11 月 1 日星期日 06:00:00 +0100)。America/New_York

下面使用专有的间隔语法:

SELECT UNIX_TIMESTAMP('2009-11-01 00:00:00' + INTERVAL 3599 SECOND); # 1257051599
SELECT UNIX_TIMESTAMP('2009-11-01 00:00:00' + INTERVAL 3600 SECOND); # 1257055200

SELECT UNIX_TIMESTAMP('2009-11-01 01:00:00' - INTERVAL 1 SECOND); # 1257051599
SELECT UNIX_TIMESTAMP('2009-11-01 01:00:00' - INTERVAL 0 SECOND); # 1257055200

甚至不会返回准确的时间。FROM_UNIXTIME()

SELECT UNIX_TIMESTAMP(FROM_UNIXTIME(1257051599)); # 1257051599
SELECT UNIX_TIMESTAMP(FROM_UNIXTIME(1257051600)); # 1257055200

奇怪的是,DATETIME 仍将在 DST 开始的“丢失”小时内存储和返回(仅以字符串形式!)时间(例如 )。但是在任何MySQL函数中使用这些日期都是有风险的:2009-03-08 02:59:59

SELECT UNIX_TIMESTAMP('2009-03-08 01:59:59'); # 1236495599
SELECT UNIX_TIMESTAMP('2009-03-08 02:00:00'); # 1236495600
# ...
SELECT UNIX_TIMESTAMP('2009-03-08 02:59:59'); # 1236495600
SELECT UNIX_TIMESTAMP('2009-03-08 03:00:00'); # 1236495600

要点:如果您需要在一年中的每个时间存储和检索,您有一些不良选择:

  1. 将系统时区设置为 GMT + 一些恒定偏移量。例如,世界协调时
  2. 将日期存储为INT(正如Aaron发现的那样,TIMESTAMP甚至不可靠)

  3. 假设 DATETIME 类型具有某个常量偏移时区。例如,如果你在MySQL之外,将你的日期转换为GMT-5,然后存储为DATETIME(事实证明这是必不可少的:参见Aaron的答案)。然后,您必须非常小心使用MySQL的日期/时间函数,因为有些假设您的值是系统时区的,而其他值(特别是时间算术函数)是“时区不可知的”(它们可能表现得好像时间是UTC)。America/New_York

Aaron 和我怀疑自动生成的 TIMESTAMP 列也被破坏了。两者都将存储为不明确的.2009-11-01 01:30 -04002009-11-01 01:30 -05002009-11-01 01:30


推荐