What is the Best Way to Represent Summer Time Rules?

Question

I need to store the summer time (daylight saving time) change-over rules for different world regions in a database. I already have a way of storing regions and sub-regions (so the whole "half of Australia"/Arizona/Navaho problem is taken care of), but I'm wondering what the most efficient schema would be to accomplish this. The two options as I see them:

• Have a table which contains unique one row for each year and region giving the start and end times for summer time as well as the specific offset
• Have a table which stores a formula and effective date range for each region (effective range required for regions like Israel)

The advantage to the first is flexibility, since literally anything is possible. Unfortunately, it also requires (a) more space, and correspondingly (b) a lot of work to get the data input. The second is nice because one row could correspond to one region for decades, but it also requires some sort of language parser and interpreter in the application layer. Since this database will be used by several different applications written in languages without powerful text processing capabilities, I would rather avoid that route.

I would love to just use zoneinfo or something like that, but unfortunately that's not an option in this case. Likewise, I cannot normalize the dates, timezone and summer time info must be in the database to satisfy certain use cases.

Does anybody have any experience doing something similar? Likewise, does anyone have any brilliant options that I may have missed?

Answer 1

You're pretty much doomed to the first option. You can pre-generate dates as far ahead as you wish for countries that have "rules" regarding time changes, but some areas do not have any rule and the changes are enacted either by dictatorial fiat or by legislative vote annually (Brazil did so until this year).

This is why all OS vendors roll out timezone file changes once or twice a year -- they have to, because they cannot generate a 100% accurate file programatically.

Answer 2

If the DST rules must be in the database, I'd probably choose to automatically update them from an external authoritative source (library, website, whatever). Manually maintaining DST rules doesn't sound like much fun.

Answer 3

The Oracle DBMS automatically handles this for you. The date is stored in an internal representation (lets imagine UMT for the sake of the argument) and is formatted according to the rules of the timezone when converted to a string.

This also solves the argument about what to do during the change over time. I.E. when you roll the clock back 1/2 hour there is actually 2 instances of 3:25 am on the same day.

Answer 4

One of the best sources of information about time zone rules is the Olson database, which is available from elsie.nci.nih.gov. The current version of the data is tzdata2008f.tar.gz, the current version of the code is tzcode2008e.tar.gz. This tends to be the source of information for many other systems (including, in particular, the Oracle information). There's a mailing list available, too. As you can see, there have been six versions of the data so far in 2008; I have copies of 2005r, 2006l, 2007k lurking on my machine, so things can change rather frequently.

There's also the Common Locale Data Repository CLDR which has information about time zones too.