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.
Advertisement
Answer
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.