I have a database that contains UTC (GMT+0) timestamps. Here is the problem: they refer to billing activity that occurred in the state of Indiana.
As some may know, Indiana ...
- did not observe Daylight Savings Time at all prior to 2006 (most of the state was EST all year long).
- voted in 2006 to remain EST, but to begin observing Daylight Savings time with the rest of the U.S.
- revised it's rules in 2007; the U.S. Federal Government revised Daylight Savings Time so that the start and end rules changed
Because the timestamps in this database refer to government billing activity, it is actually necessary to have the UTC timestamps reported as local times that are historically accurate in order to preserve auditing accuracy. There are therefore three scenarios that apply uniquely to Indiana: timestamps before 2006 use a completely different set of timezone rules than those between 2006-2007, and those after 2007 use yet another completely different set of timezone rules. You can imagine that this database includes activity for locales other than Indiana, and so things get even more complicated.
The Java Calendar and TimeZone API does not contain any classes that allow the programmer to create objects with more than one set of timezone rules and so they cannot be used for the correct mapping of UTC timestamps to historically accurate local times if the particular, applicable timezone rules ever change.
I've thought about ways to address the problem of mapping to historically accurate local times in locales with changing timezone rules...
The database could be updated so that UTC timestamps could be stored along with the local time offset and a daylight savings offset. The problem here is that the size of the database increases a bit and if the existing database is large, then updating all the tables may require an extended offline maintenance cycle.
The database could be updated so that the particular TimeZone object appropriate to that particular timestamp is stored with each timestamp. Although I think that TimeZone objects are pretty lightweight, I see this is more flexible than the above but still less desirable as it requires persisting an object for every timestamp.
A custom API can be developed that builds an appropriate TimeZone object from a database of historically accurate rules for the locales of interest. This solution replaces the increased storage requirement of the previous two solutions with the need for additional processing. Every timestamp that needed to be displayed would mean creating a new object ... or at least pairing with an appropriate object from an object pool. Plus, it means creating and administering a timezone database.
The limitations of the Java Time and Calendar API have made finding an elegant solution to this problem really harder than it should be (you can't, for example, query an existing SimpleTimeZone object and ask it what Daylight Savings Rules it follows without writing some really messy code).
I'd just like to ask if anyone else has dealt with a situation like this before and how they've handled it.