I am while using America/New You are able to timezone. Within the Fall we "fall back" an hour or so -- effectively "attaining" 1 hour at 2am. In the transition point the next happens:

it's 01:59:00 -04:00
then one minute later it might be:
01:00:00 -05:00

If you simply say "1:30am" it's ambiguous whether or otherwise you are mentioning to the very first time 1:30 comes around or even the second. I am attempting to save arranging data to some MySQL database and should not figure out how in order to save the occasions correctly.

Here's the issue:
"2009-11-01 00:30:00" is saved internally as 2009-11-01 00:30:00 -04:00
"2009-11-01 01:30:00" is saved internally as 2009-11-01 01:30:00 -05:00

This really is fine and fairly expected. But how do you save almost anything to 01:30:00 -04:00? The documentation doesn't show any support for indicating the offset and, accordingly, when I have attempted indicating the offset it has been duly overlooked.

The only real solutions I have considered involve setting the server to some timezone that does not use daylight savings some time and doing the required changes during my scripts (I am using PHP with this). But that does not appear like it ought to be necessary.

Thank you for just about any suggestions.

MySQL's date types are, frankly, damaged and can't store all occasions properly unless of course the body is placed to some constant offset timezone, like UTC or GMT-5. (I am using MySQL 5..45)

The reason being you cannot store whenever throughout the hour before Daylight Not Waste Time finishes. Regardless of how you input dates, every date function will treat these occasions as if they're throughout the hour following the switch.

My system's timezone is America/New_You are able to. Let us try storing 1257051600 (Sun, 01 November 2009 06:00:00 +0100).

Here's while using proprietary INTERVAL syntax:

Choose UNIX_TIMESTAMP('2009-11-01 00:00:00' + INTERVAL 3599 SECOND) # 1257051599

Choose UNIX_TIMESTAMP('2009-11-01 00:00:00' + INTERVAL 3600 SECOND) # 1257055200

Choose UNIX_TIMESTAMP('2009-11-01 01:00:00' - INTERVAL 1 SECOND) # 1257051599

Choose UNIX_TIMESTAMP('2009-11-01 01:00:00' - INTERVAL  SECOND) # 1257055200

Even FROM_UNIXTIME() will not return the accurate time.

Choose UNIX_TIMESTAMP(FROM_UNIXTIME(1257051599)) # 1257051599

Choose UNIX_TIMESTAMP(FROM_UNIXTIME(1257051600)) # 1257055200

Strangely enough, DATETIME will still store and return (in string form only!) occasions inside the "lost" hour when DST begins (e.g. 2009-03-08 02:59:59). But with such dates in a MySQL function is dangerous:

Choose UNIX_TIMESTAMP('2009-03-08 01:59:59') # 1236495599

Choose UNIX_TIMESTAMP('2009-03-08 02:00:00') # 1236495600

# ...

Choose UNIX_TIMESTAMP('2009-03-08 02:59:59') # 1236495600

Choose UNIX_TIMESTAMP('2009-03-08 03:00:00') # 1236495600

The takeaway: If you want to store and retrieve every time around, you've got a couple of undesirable options:

  1. Set system timezone to GMT + some constant offset. E.g. UTC
  2. Store dates as INTs (as Aaron discovered, TIMESTAMP is not even reliable)

  3. Pretend the DATETIME type has some constant offset timezone. E.g. If you are in America/New_You are able to, convert your date to GMT-5 outdoors of MySQL, then store like a DATETIME (this works out to become essential: see Aaron's answer). Then you definitely will need to take great care using MySQL's date/time functions, because some assume your values are from the system timezone, others (esp. time arithmetic functions) are "timezone agnostic" (they might become when the occasions are UTC).

Aaron and that i suspect that auto-producing TIMESTAMP posts will also be damaged. Both 2009-11-01 01:30 -0400 and 2009-11-01 01:30 -0500 is going to be saved because the ambiguous 2009-11-01 01:30.