Menu
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:
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?
789
Clocks Back or Forward? “Spring forward, fall back” is one of the little sayings used to remember which way to set your watch. You set your clock forward one hour in the spring when DST starts (= lose 1 hour), and back one hour when DST ends in the fall (= regain 1 hour).
Summer represents a time of our lives when we are strong and embarking on adventures. It symbolizes freedom, happiness, idleness and self-development. Positive times of friendship and love are often set in the summer months in books, poetry and film.
Today, most Americans spring forward (turn clocks ahead and lose an hour) on the second Sunday in March (at 2:00 A.M.) and fall back (turn clocks back and gain an hour) on the first Sunday in November (at 2:00 A.M.).
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.
126
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
