MySQL日期时间字段和夏令时间 - 如何引用“额外”小时？ [英] MySQL datetime fields and daylight savings time -- how do I reference the "extra" hour?

问题描述

我正在使用美国/纽约时区。在秋天，我们回落一个小时 - 在凌晨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）。但是这似乎不应该是必要的。

非常感谢任何建议。

解决方案

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（） t返回准确的时间。

  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 
  

外卖：如果您需要每次存储和检索在一年中，您有一些不良选择：

1. 将系统时区设置为GMT +某些常量偏移量。例如。 UTC



2. 将日期存储为INT（如Aaron发现，TIMESTAMP甚至不可靠）




3. 假装DATETIME类型有一些固定的偏移时区。例如。如果您在 America / New_York 中，将您的日期转换为MySQL 之外的GMT-5 ，然后作为DATETIME存储至关重要：见亚伦的回答）。那么你必须非常小心使用MySQL的日期/时间函数，因为有些人假设你的值是系统时区，其他的（特别是时间算术函数）是时区不可知论（它们的行为就像时间是UTC） / p>

Aaron和我怀疑自动生成TIMESTAMP列也被打破了。 2009-11-01 01:30 -0400 和 2009-11-01 01:30 -0500 将会存储为不明确的 2009-11-01 01:30 。

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:

it's 01:59:00 -04:00
then 1 minute later it becomes:
01:00:00 -05:00

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.

Here's the problem:
"2009-11-01 00:30:00" is stored internally as 2009-11-01 00:30:00 -04:00
"2009-11-01 01:30:00" is stored internally as 2009-11-01 01:30:00 -05: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.

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'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.

My system's timezone is America/New_York. Let's try storing 1257051600 (Sun, 01 Nov 2009 06:00:00 +0100).

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

Even FROM_UNIXTIME() won't return the accurate time.

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

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:

1. Set system timezone to GMT + some constant offset. E.g. UTC

2. Store dates as INTs (as Aaron discovered, TIMESTAMP isn't even reliable)

3. 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 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屋！