UTC time in SQL tables

Question

Columns such as CreatedDate and ModifiedDate are commonly used in the SQL tables. I think, storing the UTC value of the time would help remove dependency on the location of the server hosting the app. With the cloud computing gaining familiarity(even excluding it), the applications could be hosted in any timezone and in any part of the world.

By using UTC values for such fields, the business logic can always assume the fields to be UTC and convert the date per localization requirements.

Is my thought process right? Are there any cons in this approach? Does it make easier to store time specific to a zone instead?

Appreciate your thoughts!

Answer 1

IMHO even local only applications should store UTC, if not for other reason then at least to handle correctly daylight savings time adjustment. For anything distributed, UTC is a must.

The client can then display the time localized, since all client platforms have support to display UTC as local time.

Answer 2

SQL 2008 has a data type called datetimeoffset, which can store the timezone as well. This means that you can have your cake and eat it!

You kinda do need to store the UTC version, because otherwise you have "which 2:30am was that?" whenever daylight savings occurs. But with datetimeoffset you're fine.

If you just store the UTC version, then you need to consider which timezone your client is in to display it for them. But then when daylight savings occurs, all their times move by an hour, so you're still having no luck.

Interestingly, Mitch Wheat (who posted a comment on your question), lives in Perth, who only recently introduced daylight savings. I wrote a blog about that at: http://msmvps.com/blogs/robfarley/archive/2006/10/25/The-horror-of-daylight-savings-_2800_sorry-Perth_2900_.aspx

So, see how you go. I'd definitely recommend datetimeoffset, so that you can show that the time was stored as 5pm in summertime.

Rob