I recently worked on an application that captured date and time information across time zones and needed to translate the times into the local time zone of the user. Thus began my journey down the road of time zone alphabet soup.
Over the years Microsoft has introduced several new tools that make time zone translation much easier. A key one is the SQL Server DateTimeOffset data type which was introduced with SQL Server 2008. This field is a date and time data type that also stores the time zone offset. Time can be stored in whatever time zone is desired but, along with the time, the time offset is stored as well. With the time offset in hand, time can be translated into any other time zone or to Greenwich Mean Time (GMT) for cross regional comparisons. Additional SQL Server functions, such as the switchoffset function, make the translation a breeze. DateTimeOffset has eliminated the need to store the time zone offset in a different field and the SQL Server functions make the translation painless.
However, SQL Server does not support daylight savings time translation. Daylight Savings Time (DST) versus Standard Time (ST) is the big Achilles’ heel.
DST, as I am sure you know, is the policy of moving the clocks one hour forward each spring and then reverting back to ST in the fall. This policy is observed in most states in the United States, but not all. The date that it begins and ends each year is not a hard date, but rather starts on the second Sunday of March and ends on the first Sunday of November. Therefore, in order to know how to translate time from one time zone to another, you also have to know, (1) does that geographic area apply DST and (2) is DST in effect on that date.
Now, the .NET framework does have some tools to help manage this tangled web. And, the tools in the .NET framework can be used, in many instances, to create CLR procedures and functions to extend SQL Server functionality. For example, there is the TimeZoneInfo class that was introduced in the .NET 3.5 framework. This class has methods which can translate time within the context of DST, and can do so in a regional context. For example, the ConvertTime method converts time differently for Arizona which does not apply DST vs. Colorado, which does.
But, there is another gotcha. The TimeZoneInfo class is built with the “HostProtectionAttribute” of “MayLeakOnAbort”. As a result, this class cannot be compiled into a SQL Server CLR procedure without a setting of UNSAFE. Most clients I work with would not want to allow UNSAFE code to be compiled in their database.
The .NET framework does have an older class dating all the way back to the 1.1 framework days, called the TimeInfo class. However, this class only has knowledge around the time zone of the server and not any other time zone.
The application I was working with had one more complexity. Dates and times were captured by a variety of input sources. One source was a handheld device, which stored time in the time zone of the store. The other source was a web application and a web service, which stored time in the time zone of the server. Lastly, some data came over from a different system all together. Providing a cohesive, consistent date time model was going to be critical for the final output.
So, I would have to support my solution with some additional information after all. To wrap up this convoluted tale, here is the design I finally decided on.
1. SQL Server DateTimeOffset field.
This field would store my date and time in the time zone of the local time zone. Displaying the time in the local time zone was the most common need for reporting. Storing it this way would eliminate the need to translate the data for every report. However, I did not want to eliminate the ability to do cross regional analysis. The DateTimeOfffset data type also stored the time zone offset, allowing translation to GMT.
2. SQL Server bit field for IsDaylightSavingsTime
Since each year the cut over date for DST changes, storing this simple bit flag would tell me whether the date was “in the zone” or not, without having to maintain a separate calendar table that stored the beginning and ending dates for DST for each year. At the point that the date is captured, it was easy to use the TimeInfo class to determine if the date was within the range of DST or not. (This may not have been as easy if the dates were historical dates but, in this instance, the dates were current dates.)
3. An additional SQL Server table by store.
This table stored the store id, the Daylight Savings Time (DST) offset for the store and the Standard Time (ST) offset for the store. If the region for the store does not apply DST, then both offsets were the same.
With this design, I was able to convert all dates to the local time zone of the store, and still preserve the ability to convert all times to the universal GMT for broader time comparisons.
If all we had were time zones to worry about, preparing the “soup” would be as easy as “opening a can”. But, with that extra human spice of DST, Time Zone Alphabet soup requires much more preparation.
One final footnote, this exercise led me to research why we have this crazy DST setup to negotiate. Here are a few fun facts about DST:
• DST was first suggested in a whimsical essay by Benjamin Franklin in 1784. Benjamin Franklin had just seen a demonstration of a new oil lamp. It prompted him to do a parody on himself and his desire for thrift, and so he calculated how much money in candles was wasted, because people rise too late in the morning and waste hours of natural daylight (http://www.webexhibits.org/daylightsaving/franklin.html).
• The United States first implemented DST in 1918 during WWI, to conserve coal during wartime. However, the farmers did not like DST, since they almost always rose earlier than daylight regardless of the clock, and so it was repealed in 1919. President Wilson vetoed it twice, but Congress overrode the veto the second time.
• By 1945 some states and localities were implementing DST while others were not. Radio and TV stations and the transportation companies had to publish new schedules every time a state or town began or ended DST ( http://www.webexhibits.org/daylightsaving/e.html).
• In the early 1960’s the Committee for Time Uniformity discovered that on the 35-mile stretch of highway (Route 2) between Moundsville, W.V., and Steubenville, Ohio, the time zone changed seven times (http://www.webexhibits.org/daylightsaving/e.html).
• In the mid-1980s, Clorox (parent of Kingsford Charcoal) and 7-Eleven provided the primary funding for the Daylight Saving Time Coalition, and both Idaho senators voted for it based on the premise that during DST fast-food restaurants sell more French fries, which are made from Idaho potatoes (http://en.wikipedia.org/wiki/Daylight_saving_time)