MySQL 日期时间字段和夏令时——我如何引用“额外"字段?小时? [英] MySQL datetime fields and daylight savings time -- how do I reference the "extra" hour?
问题描述
我使用的是美国/纽约时区.在秋季，我们退回"一小时——实际上是在凌晨 2 点增加"一小时.在转换点发生以下情况:
I'm using the America/New York timezone. In the Fall we "fall back" an hour -- effectively "gaining" one hour at 2am. At the transition point the following happens:
现在是 01:59:00 -04:00
然后 1 分钟后它变成:
01:00:00 -05:00
it's 01:59:00 -04:00
then 1 minute later it becomes:
01:00:00 -05:00
因此，如果您只是说凌晨 1:30"，那么您指的是第一次 1:30 还是第二次出现是模棱两可的.我正在尝试将调度数据保存到 MySQL 数据库中，但无法确定如何正确保存时间.
So if you simply say "1:30am" it's ambiguous as to whether or not you're referring to the first time 1:30 rolls around or the second. I'm trying to save scheduling data to a MySQL database and can't determine how to save the times properly.
问题来了:
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?文档 不支持指定偏移量，并且，因此，当我尝试指定偏移量时，它已被适当忽略.
This is fine and fairly expected. But how do I save anything to 01:30:00 -04:00? The documentation does not show any support for specifying the offset and, accordingly, when I've tried specifying the offset it's been duly ignored.
我想到的唯一解决方案是将服务器设置为不使用夏令时的时区并在我的脚本中进行必要的转换(为此我使用 PHP).但这似乎没有必要.
The only solutions I've thought of involve setting the server to a timezone that doesn't use daylight savings time and doing the necessary transformations in my scripts (I'm using PHP for this). But that doesn't seem like it should be necessary.
非常感谢您的任何建议.
Many thanks for any suggestions.
推荐答案
坦率地说，MySQL 的日期类型已损坏，并且无法正确存储所有时间，除非您的系统设置为恒定偏移时区，例如UTC 或 GMT-5.(我使用的是 MySQL 5.0.45)
MySQL's date types are, frankly, broken and cannot store all times correctly unless your system is set to a constant offset timezone, like UTC or GMT-5. (I'm using MySQL 5.0.45)
这是因为您无法在夏令时结束前的一小时内存储任何时间.无论您如何输入日期，每个日期函数都会将这些时间视为切换后的一小时内.
This is because you can't store any time during the hour before Daylight Saving Time ends. No matter how you input dates, every date function will treat these times as if they are during the hour after the switch.
我系统的时区是 America/New_York.让我们尝试存储 1257051600(Sun, 01 Nov 2009 06:00:00 +0100).
My system's timezone is America/New_York. Let's try storing 1257051600 (Sun, 01 Nov 2009 06:00:00 +0100).
这里使用专有的 INTERVAL 语法:
Here's using the proprietary INTERVAL syntax:
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() 也不会返回准确的时间.
Even FROM_UNIXTIME() won't return the accurate time.
SELECT UNIX_TIMESTAMP(FROM_UNIXTIME(1257051599)); # 1257051599
SELECT UNIX_TIMESTAMP(FROM_UNIXTIME(1257051600)); # 1257055200
奇怪的是，DATETIME 将仍然在 DST 开始的丢失"小时内存储和返回(仅以字符串形式！)时间(例如 2009-03-08 02:59:59).但是在任何 MySQL 函数中使用这些日期都是有风险的:
Oddly enough, DATETIME will still store and return (in string form only!) times within the "lost" hour when DST starts (e.g. 2009-03-08 02:59:59). But using these dates in any MySQL function is risky:
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
要点:如果您需要在一年中每次存储和检索，您有一些不受欢迎的选择:
The takeaway: If you need to store and retrieve every time in the year, you have a few undesirable options:
- 将系统时区设置为 GMT + 一些恒定偏移量.例如.世界标准时间
将日期存储为 INT(正如 Aaron 发现的那样，TIMESTAMP 甚至不可靠)
- Set system timezone to GMT + some constant offset. E.g. UTC
Store dates as INTs (as Aaron discovered, TIMESTAMP isn't even reliable)
假设 DATETIME 类型有一些恒定的偏移时区.例如.如果您在 America/New_York，请将您的日期转换为 GMT-5 在 MySQL 之外，然后存储为 DATETIME(事实证明这是必不可少的:请参阅 Aaron 的回答).那么你必须非常小心地使用 MySQL 的日期/时间函数，因为有些假设你的值是系统时区的，其他的(尤其是时间算术函数)是时区不可知的"(它们的行为可能就像时间是 UTC).
Pretend the DATETIME type has some constant offset timezone. E.g. If you're in America/New_York, convert your date to GMT-5 outside of MySQL, then store as a DATETIME (this turns out to be essential: see Aaron's answer). Then you must take great care using MySQL's date/time functions, because some assume your values are of the system timezone, others (esp. time arithmetic functions) are "timezone agnostic" (they may behave as if the times are UTC).
Aaron 和我怀疑自动生成的 TIMESTAMP 列也被破坏了.2009-11-01 01:30 -0400 和 2009-11-01 01:30 -0500 都将存储为二义性 2009-11-0101:30.
Aaron and I suspect that auto-generating TIMESTAMP columns are also broken. Both 2009-11-01 01:30 -0400 and 2009-11-01 01:30 -0500 will be stored as the ambiguous 2009-11-01 01:30.
这篇关于MySQL 日期时间字段和夏令时——我如何引用“额外"字段?小时?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
