MySQL datetime fields and daylight savings time -- how do I reference the "extra" hour?

I've got it figured out for my purposes. I'll summarize what I learned (sorry, these notes are verbose; they're as much for my future referral as anything else).

Contrary to what I said in one of my previous comments, DATETIME and TIMESTAMP fields do behave differently. TIMESTAMP fields (as the docs indicate) take whatever you send them in "YYYY-MM-DD hh:mm:ss" format and convert it from your current timezone to UTC time. The reverse happens transparently whenever you retrieve the data. DATETIME fields do not make this conversion. They take whatever you send them and just store it directly.

Neither the DATETIME nor the TIMESTAMP field types can accurately store data in a timezone that observes DST. If you store "2009-11-01 01:30:00" the fields have no way to distinguish which version of 1:30am you wanted -- the -04:00 or -05:00 version.

Ok, so we must store our data in a non DST timezone (such as UTC). TIMESTAMP fields are unable to handle this data accurately for reasons I'll explain: if your system is set to a DST timezone then what you put into TIMESTAMP may not be what you get back out. Even if you send it data that you've already converted to UTC, it will still assume the data's in your local timezone and do yet another conversion to UTC. This TIMESTAMP-enforced local-to-UTC-back-to-local roundtrip is lossy when your local timezone observes DST (since "2009-11-01 01:30:00" maps to 2 different possible times).

With DATETIME you can store your data in any timezone you want and be confident that you'll get back whatever you send it (you don't get forced into the lossy roundtrip conversions that TIMESTAMP fields foist on you). So the solution is to use a DATETIME field and before saving to the field convert from your system time zone into whatever non-DST zone you want to save it in (I think UTC is probably the best option). This allows you to build the conversion logic into your scripting language so that you can explicitly save the UTC equivalent of "2009-11-01 01:30:00 -04:00" or ""2009-11-01 01:30:00 -05:00".

Another important thing to note is that MySQL's date/time math functions don't work properly around DST boundaries if you store your dates in a DST TZ. So all the more reason to save in UTC.

In a nutshell I now do this:

When retrieving the data from the database:

Explicitly interpret the data from the database as UTC outside of MySQL in order to get an accurate Unix timestamp. I use PHP's strtotime() function or its DateTime class for this. It can not be reliably done inside of MySQL using MySQL's CONVERT_TZ() or UNIX_TIMESTAMP() functions because CONVERT_TZ will only output a 'YYYY-MM-DD hh:mm:ss' value which suffers from ambiguity problems, and UNIX_TIMESTAMP() assumes its input is in the system timezone, not the timezone the data was ACTUALLY stored in (UTC).

When storing the data to the database:

Convert your date to the precise UTC time that you desire outside of MySQL. For example: with PHP's DateTime class you can specify "2009-11-01 1:30:00 EST" distinctly from "2009-11-01 1:30:00 EDT", then convert it to UTC and save the correct UTC time to your DATETIME field.

Phew. Thanks so much for everyone's input and help. Hopefully this saves someone else some headaches down the road.

BTW, I am seeing this on MySQL 5.0.22 and 5.0.27

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.

I think micahwittman's link has the best practical solution to these MySQL limitations: Set the session timezone to UTC when you connect:

SET SESSION time_zone = '+0:00'

Then you just send it Unix timestamps and everything should be fine.

But how do I save anything to 01:30:00 -04:00?

You can convert to UTC like:

SELECT CONVERT_TZ('2009-11-29 01:30:00','-04:00','+00:00');

You can convert from UTC to localtime using CONVERT_TZ:

SELECT CONVERT_TZ(UTC_TIMESTAMP(),'+00:00','SYSTEM');

Where '+00:00' is UTC, the from timezone , and 'SYSTEM' is the local timezone of the OS where MySQL runs.

This thread made me freak since we use TIMESTAMP columns with On UPDATE CURRENT_TIMESTAMP (ie: recordTimestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP) to track changed records and ETL to a datawarehouse.

In case someone wonder, in this case, TIMESTAMP behave correctly and you can differentiate between the two similar dates by converting the TIMESTAMP to unix timestamp:

select TestFact.*, UNIX_TIMESTAMP(recordTimestamp) from TestFact;

id  recordTimestamp         UNIX_TIMESTAMP(recordTimestamp)
1   2012-11-04 01:00:10.0   1352005210
2   2012-11-04 01:00:10.0   1352008810