I saw a post asking if an application ought to store time data as UTC and handle conversions on the client, or should they store data as the client inserts it. I suppose the answer is "it depends", but should we have this decision? Shouldn't we just always store time data as UTC based and convert it at the client?
Dealing with times, time zones, and the proper way to calculate data is complicated. We recently had a question of the day that asked if times were equivalent, given different numerical times across time zones, and there was quite a debate as to whether the times given were actually equivalent or equal. That was a frustrating debate for me to referee and I am very hesitant to allow other questions dealing with time zones for the time being.
For this Friday's poll, I wanted to ask the question of the rest of you and understand how the rest of you look at time values and their impact on our applications. Let us know your answer to this question:
Should we always store time values as UTC in SQL Server?
It might sound a little silly, a little outrageous to store all time values as some reference time and then offset the hours by a time zone. After all, you could even be offsetting by a date as well if enough time zones are crossed. However I think there might be some merit in this.
While almost all applications I've dealt with stored date times as local time, there were occasions where users in different time zones accessed the application. And we hadn't planned for it. It didn't always cause problems, but if we had stored all data as UTC, as a reference time, we would never have confusion. At least not in the data stored. I supposed that applications might still have to decide what local time to use when inserting data, but the server would have have the question of a row of data being EST or CST.
As we grow more interconnected, with users across the globe accessing our applications, I think there becomes a need for a standard time storage mechanism. I'm actually surprised that more auditing organizations haven't required it. After all, if you are trying to determine when a sale took place, or a system access occurred, it shouldn't ever have the chance of being off by an hour or more.
Steve Jones
The Voice of the DBA Podcasts
The podcast feeds are available at sqlservercentral.mevio.com. You can also follow Steve Jones on Twitter:
or now on iTunes!
- Windows Media Podcast - 28.6MB WMV
- iPod Video Podcast - 20.0MB MP4
- MP3 Audio Podcast - 4.5MB MP3
Today's podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.
I really appreciate and value feedback on the podcasts. Let us know what you like, don't like, or even send in ideas for the show. If you'd like to comment, post something here. The boss will be sure to read it.