mysql时间字段中又t和z怎么处理,MySQL日期时间字段和夏令时间 - 如何引用“额外”小时？...

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:

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

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

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.