[grimlck]

Storing everything in UTC is a huge improvement, but is still an imperfect solution because the Tz database changes over time.

Suppose you are writing a appointment tracking system, and, at some point before the olson tz database has been updated with this change, the user in the Samoa timezone creates an future appointment for Jun 2, 2012. That would be stored in UTC.

Now, you upgrade the olson TZ database file on your server to a newer version, which incorporates the Somoa tz change. Now, without any user input, the appointment is suddenly on Jun 3, 2012! (because the same UTC date, with the updated TZ database, is Jun 3, 2012)

Is that what a user would have expected if they created the appointment for Jun 2, 2012? I would say no. But that said, what if the appointment was for Dec 30 2011? That date no longer exists, so it would have be pushed ahead automatically.

Conclusion: time zones are hard

[natrius]

My assumption was that the TZ database noted when changes in time zones happened so there was always an accurate one-to-one mapping between a Unix time and a time in each time zone. Adding 2012's time zone information wouldn't override 2010's. Is this incorrect?

[rmc]

> there was always an accurate one-to-one mapping between a Unix time and a time in each time zone

That's not true in general. When the "clocks go backwards", there are two 1:30ams on that morning.

The bug this comment is refering to, is where you convert from local to UTC as soon as the user enters the datetime, and then use that from then on. Since the "local to UTC" conversion might change between now and the date, as the tzdata time is updated, you might get this bug.

[mikeash]

It seems to me that it's impossible to solve this bug in the general case. If the user enters March 15, 2012 before the switch, should that become March 16, 2012 after the switch, or stay March 15? Either scenario could be correct depending on the intent of the date. Messy.

[elefont2]

it does, but you cannot predict future legislation.

In 2010, you may not yet know that the timezones will change in 2012, but you might still need to store a 2012 date in your database.

So, the best you can do, if storing in UTC, is store that 2012 date using 2010 timezone rules, as you don't yet know what the 2012 time zone rules are going to be

[protomyth]

The last system I had to do that had to have good dates - I did the following:

Past events had the local time, UTC, and time zone entry stored. Storage isn't much of an issue and it helped to keep things straight. Future events were only stored in local (8pm Dec 12, 2012) with time zone. Calculations were done on UTC with future events computing UTC on the fly. Reporting and actual day something occurred was kind of important. A day skipped would be an issue.

[rmc]

Yes, you're right. In cases like that you should store future events as local time, and wait until a resonable time before the event (e.g. a week) to convert from local to UTC. Then you will have (hopefully) an updated tzdata file.

[notatoad]

what? no, that is a horribly convoluted solution to account for a rare edge case. store UTC and an olson string, and if people live in an area where the dates change, it is their responsibility to move their appointments. if you want to be really nice, you could run a batch job to switch all effected events a day forward.

[andrewaylett]

I disagree -- in this case, you should consider storing local time and the Olson tz it's due to be in. Microsoft make the mistake of doing it your way in Outlook, meaning that everyone had to update their calendars when the US changed their DST rules.

Events in the future are commonly scheduled relative to what the time is going to be in a specific place, regardless of how the tz rules change in the meantime. Automated actions in the future, however, may well need to be fixed to a UTC time. In short: you need to think about how your system will actually be used, and possibly store values differently depending on how they will be used.

On no account ever use a raw UTC+n offset.