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

Question

我正在使用美国/纽约时区。在秋季，我们“退后”一个小时-实际上是在凌晨2点“获得”一个小时。在过渡点发生以下情况：

它是01:59:00 -04：00，

然后一分钟后变成：01 :00 :

00 -05：00

因此，如果您只是简单地说“ 1:30 am”，那么您是指第一次还是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）。但这似乎没有必要。

非常感谢您的任何建议。

Answer 1

坦率地说，MySQL的日期类型已损坏，无法正确存储所有时间，除非将系统设置为恒定的偏移时区，例如UTC或GMT-5。（我正在使用MySQL 5.0.45）这是因为您无法在夏令时结束前的一个小时内存储任何时间。无论您如何输入日期，每个日期函数都会将这些时间视为在切换后的一小时内。我的系统的时区为America/New_York。让我们尝试存储1257051600（星期日，2009年11月1日06:00:00 +0100）。这里使用专有的INTERVAL语法：SELECT UNIX_TIMESTAMP('2009-11-01 00:00:00' + INTERVAL 3599 SECOND); # 1257051599SELECT UNIX_TIMESTAMP('2009-11-01 00:00:00' + INTERVAL 3600 SECOND); # 1257055200SELECT UNIX_TIMESTAMP('2009-11-01 01:00:00' - INTERVAL 1 SECOND); # 1257051599SELECT UNIX_TIMESTAMP('2009-11-01 01:00:00' - INTERVAL 0 SECOND); # 1257055200甚至FROM_UNIXTIME()不会返回准确的时间。SELECT UNIX_TIMESTAMP(FROM_UNIXTIME(1257051599)); # 1257051599SELECT UNIX_TIMESTAMP(FROM_UNIXTIME(1257051600)); # 1257055200奇怪的是，DSTTIME 仍将在DST开始的“丢失”时间内存储和返回（仅以字符串形式！）时间（例如2009-03-08 02:59:59）。但是在任何MySQL函数中使用这些日期都是有风险的：SELECT UNIX_TIMESTAMP('2009-03-08 01:59:59'); # 1236495599SELECT UNIX_TIMESTAMP('2009-03-08 02:00:00'); # 1236495600# ...SELECT UNIX_TIMESTAMP('2009-03-08 02:59:59'); # 1236495600SELECT UNIX_TIMESTAMP('2009-03-08 03:00:00'); # 1236495600要点：如果您需要在一年中的每个时间进行存储和检索，则有一些不受欢迎的选择：将系统时区设置为GMT +一些恒定的偏移量。例如UTC将日期存储为INT（如Aaron发现的，TIMESTAMP甚至不可靠）假设DATETIME类型具有一些恒定的偏移时区。例如，如果您在使用America/New_York，请将日期转换为MySQL之外的 GMT-5 ，然后将其存储为DATETIME（事实证明这很重要：请参阅Aaron的回答）。然后，您必须格外小心地使用MySQL的日期/时间函数，因为某些函数假设您的值是系统时区，而其他函数（尤其是时间算术函数）则是“时区不可知的”（它们的行为就像是UTC一样）。Aaron和我怀疑自动生成的TIMESTAMP列也已损坏。两者2009-11-01 01:30 -0400和2009-11-01 01:30 -0500将被存储为模棱两可2009-11-01 01:30。

Answer 2

由于我们使用TIMESTAMP带有On UPDATE CURRENT_TIMESTAMP（即：）的列recordTimestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP来跟踪更改的记录和ETL到数据仓库，所以这个线程让我感到异常。如果有人怀疑这种情况下的TIMESTAMP行为正确，并且可以通过将TIMESTAMPunix时间戳转换为两个相似的日期来区分它们：select TestFact.*, UNIX_TIMESTAMP(recordTimestamp) from TestFact;id  recordTimestamp         UNIX_TIMESTAMP(recordTimestamp)1   2012-11-04 01:00:10.0   13520052102   2012-11-04 01:00:10.0   1352008810