I have a database that contains UTC timestamps (GMT + 0). Here's the problem: they relate to billing activity that occurred in Indiana.
How some may know, Indiana ...
- there was no daylight saving time at all until 2006 (most of the state was EST throughout the year).
- voted in 2006 to remain est, but start observing daylight saving time with the rest of the US.
- revised its rules in 2007; The U.S. federal government has revised daylight saving time to change the start and end rules.
Since the timestamps in this database are related to government payment activities, it is actually necessary that UTC timestamps be reported as local moments that are historically accurate in order to maintain audit accuracy. Thus, there are three scenarios that apply unambiguously to Indiana: timestamps before 2006 use a completely different set of time zone rules than those between 2006-2007, and those that use another completely different set after 2007 time zone 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 APIs do not contain classes that allow the programmer to create objects with more than one set of time zone rules, and therefore they cannot be used to correctly display UTC timestamps in historically accurate local time, unless the specific, applicable time zone rules are all time is changing.
I was thinking of ways to solve the problem of comparing with historically accurate local times in locales with changing time zone rules ...
The database can be updated so that UTC timestamps can be stored along with a local time offset and daylight saving time offset. The problem here is that the size of the database is slightly increasing, and if the existing database is large, then an extended offline maintenance cycle may be required to update all tables.
The database can be updated so that a specific TimeZone object corresponding to that particular time stamp is stored with each time stamp. Although I believe that TimeZone objects are quite lightweight, I see that it is more flexible than higher, but still less desirable, since it requires saving the object for each timestamp.
A custom API can be developed that builds the corresponding TimeZone object from a database with historically accurate rules for areas of interest. This solution replaces the increased storage requirement of the previous two solutions with the need for additional processing. Each timestamp that should be displayed means creating a new object ... or at least pairing it with the corresponding object from the pool of objects. In addition, this means the creation and administration of a time zone database.
The limitations of the Java Time and Calendar APIs made finding an elegant solution to this problem really more difficult than it should be (you cannot, for example, request an existing SimpleTimeZone object and ask it what follows from the summer saving rules without writing some really dirty code )
I just would like to ask if someone else has dealt with this situation before and how they dealt with it.
scottb
source share