Ok, maybe I’m late to the presses with this one, but given that no one I’ve talked to recently knows about this, I think it’s worthy of more press…
The .Net DateTime structure is a nice handy structure that we basically can’t escape. Funny thing about users, they have a tendency to want to know when they did something, when they should do something, and how far overdue it is when they didn’t do it.
The problem with .Net’s DateTime is the lack of time zone information in the structure. Believe it or not. Yeah, really.
In .Net 1, we had no indicator of what time zone it was for. In .Net 2, they introduced the notion of “kind”, but that was limited to indicating “Local”, “UTC” (i.e. GMT or Zulu), and “Unspecified”. Even with .Net 2, this led to most of us just taking the convention of immediately converting everything to UTC. Unfortunately, that led to more than one bug when someone forgot to convert or double converted (or triple or quadruple or…).
MS’s answer was always that DateTime was a 64 bit structure that they used something like 62 bits for it. They were able to add in the “kind” by using the extra 2 bits, but that only allows for 4 possible values if you recall your bitwise arithmetic (00, 01, 10, 11). Hence, Local(10), UTC(01), Unspecified (00). Maybe you’re thinking that “we only need 24”, but that doesn’t account for India or Newfoundland (something + .5 hours offset) and leaves you scratching your head at what to do with daylight savings time.
Worse, SQL Server’s DateTime only
stored the date and time with no idea on time zone. However, there at least we could just say “server time” and be done with it.
This is manageable and usually not too
much of a problem in self contained systems. Where it becomes painful is in situations where you need to serialize data and send it somewhere else. Then you really
need to worry about that time zone and it gets really goofy when you’re working with datasets. Once I get over the psychological trauma of dealing with datasets and time zones, I might be able to blog about that.
Since VS2008/.Net 3.5 and SQL 2008, we’ve had a new and vastly improved way of handling this. This is called DateTimeOffset. Unfortunately, the name seems to throw everyone off. DateTimeOffset does not just store the offset
(I.e. –5 from UTC), it stores the entire date time (2/2/2011 9:56:12 PM -05:00). Now,the interesting part is that this takes a TimeSpan, so you really can make this as flexible as you want it. TimeZoneInfo helps you with finding timezones and applying the various rules such as daylight savings time. Both just plain work with SQL server.
Short of it is that I plan on forgetting all about DateTime and only using DateTimeOffset from now on. Now I’ll always know what time zone I meant and not have to figure out what rules I need to apply to get it into local time.
Once I figure out the gotchas, I’ll relay those too.