I'm currently planning a project where I hope to extensively use DateTimeOffsets (the project will have a global user base). I'm using MSSQL 2008 and wondering what the difference is between these two scenarios:
1) Storing UTC dates with the standard datetime type, and calculating offsets at the UI layer 2) Storing UTC dates with the datetimeoffset type (I'm having a hard time seeing what the advantages are) and calculating offsets at the UI layer
I guess a broader question could be is if anyone can explain when I should be using datetimeoffset as a database column type, and what the advantages are over scenario #1 I listed above.
A huge number of sites get date/time calculations wrong.
The first wrong assumption is where webmasters assume that UK clock time is UTC or GMT.
The UK uses UTC only in the Winter and uses UTC+1 in the Summer.
Your users may or may not live in a place where they have to adjust their own clocks for DST (places near the equator don't use DST).
The server time may get adjusted twice per year for DST, or it may not.
In March, the Northern hemisphere adds an hour, but different countries do this on different dates - spread over a month or more. At the same time, places in the Southern hemisphere are taking an hour off.
In October, the Northern hemisphere takes an hour off, while the Southern hemisphere adds an hour.
These details trip a large number of programmers up.
All "events" should be recorded in UTC date/time in the database. Each user should have a UTC offset recorded (and maybe a country code and DST flag too - so that the date of DST changeover can be pre-programmed by the site owner using data from e.g. [timeanddate.com...] so the DST changeover is handled automatically for users).
The user's UTC offset (and DST offset) should be applied to the event data retrieved from the database, in order to show the date/time in the user's own zone. That "localised" date/time is calculated for each user, each time the data is displayed.
There's several offsets in the system: - Server local time > UTC in database - UTC in database > individual users local date/time.
The first one of those is often overlooked. I often see sites that say "the time right now (in UTC) is ...." and the displayed time is actually incorrect by one hour.
New York is UTC-5 in the New York Winter but is only UTC-4 in Summer. If your server is located there, you might need to alter the "server's UTC offset figure" when New York goes on and off DST.
Why can't you just use the database server time and allow the user to choose their offset? Then all you need to do is store the offset as an integer and do date math for all displays. Seems to solve all issues that way.