MySQL的日期类型,坦率地说,破碎,不能正确存储所有时间,除非您的系统设置为常量偏移时区,如UTC或GMT-5。 (我使用MySQL 5.0.45)
这是因为您不能在夏令时结束前的一小时内存储任何时间。不管你如何输入日期,每个日期函数都会将这些时间视为在切换后的一小时内。
我的系统的时区为America / New_York。让我们尝试存储1257051600(Sun,01 Nov 2009 06:00:00 0100)。
这里使用专有的INTERVAL语法:
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开始时(例如2009-03-08 02:59:59)的“丢失”小时内存储和返回(以字符串形式)!但在任何MySQL函数中使用这些日期是有风险的:
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
外卖:如果你需要在每年的时间存储和检索,你有一些不良的选择:
>将系统时区设置为GMT一些常数偏移。例如。世界标准时间
>将日期存储为INT(如Aaron发现,TIMESTAMP甚至不可靠)
>假设DATETIME类型有一些常量偏移时区。例如。如果你在America / New_York,将你的日期转换为GMT以外的MySQL,然后存储为DATETIME(这是必不可少的:见Aaron的答案)。然后你必须非常小心使用MySQL的日期/时间函数,因为有些人认为你的值是系统时区,其他(特别是时间算术函数)是“时区无关”(他们可能表现得像时间是UTC)。
Aaron和我怀疑自动生成TIMESTAMP列也被破坏。 2009-11-01 01:30 -0400和2009-11-01 01:30 -0500将被存储为模糊的2009-11-01 01:30。