Tuesday, March 18, 2008

Timezones and the FUTURE!

After reading Novell's report of the DST problem in GroupWise, I understand that a lot of these are software faults, and not problems with the OS or the timezone settings themselves.

When dealing with multiple timezones, what will a developer do? They’ll convert it to UTC for storage and comparison and then convert it back for display. This solution appears to completely remove the timezone from the problem and gives the developer a nice, sortable value to store.

Everything works fine until someone changes the start or end of DST. Immediately, all of the timestamps that were previously created for that period between the old and new start (and end) dates will be out by an hour.

If you’ve got a monthly/weekly recurring meeting it will be out by an hour into perpetuity for that 1 week every year.

The real fix is to store dates/times in the originator’s timezone and then convert them on the fly. It makes sorting a pain in the ass (time is no longer a single int!), but we’ve got CPU to burn so why not?

One final gotcha with that. You CANNOT store the originator's timezone as an offset! It needs to be a name used to retrieve the TZ information. If it is stored as an offset (I'm looking at you Oracle), you have the exact same problem. Using a name means that it becomes difficult to create an index across timestamps with mixed timezones, but the values will be correct!

The lesson to take away? You cannot convert a timestamp to UTC for storage if that timestamp is in the future.


Bwooce said...

The collary is that you should store the offset for past dates, or at least store the date with the time so you can work it out with the Olsen data. Assuming of course that the exact time is important for some reason...

Jason Pollock said...

I think that zoneinfo is historically accurate? So, you could conceivably store historic information in UTC without loss of information (given a working zoneinfo database).

I have to admit, I don't know what "Olsen data" is. What is it?