Why should you use DateTimeUTC and not DateTime in your SaaS systems? 4guysFromRolla actually has a pretty good answer from 2007:

The primary advantage of storing date/time values in UTC is that it makes the data transportable. To see what I mean, imagine that following scenario: you have an eCommerce website that is being hosted in a web server located in the Pacific time zone (UTC -8) and this application stores the date and time orders were placed in server time. Say a user, Bob, makes an order on August 1, 2007 at 9:00 AM UTC -8. After many months of phenomenal growth, you decide to switch to a larger web hosting company, one on the east coast where the time zone is UTC -5. Since the date/time is stored in server time, Bob's previous order still shows that it was made on August 1 2007 at 9:00 AM. But since we are now in UTC -5, it is as if Bob's order was made three hours earlier than it really was (since when it was 9:00 AM on August 1, 2007 in the west coast it was really 12:00 noon on the east coast).

One way around this, you might contend, is to execute a SQL query that adds three hours to the order date for all records in the table. Something like:

UPDATE Orders SET

OrderDate = DATEADD(hh, 3, OrderDate)

And such an approach would suffice... for this situation. But imagine that you moved to a web hosting company situated in the US state of Arizona, where daylight savings is not observed. E[s]p. Now you would have to write a more complex UPDATE statement that adjusted the hours based on whether the order date fell within daylight savings. Ick.

...

Things like daylight savings further complicate working with dates and times, but UTC does not observe any time zone, simplifying things a tiny bit. [emph mine -mfn]

To make a long story short, you have two choices:

  • Use DataTimeUTC and use built-in converters to get "local" time, usually on the client

  • Pick another "native" time zone for your system, and prepare to maintain your own converters

Seems an easy enough choice to me.

Labels: , , ,