Converting asp.net/sql web app to be ime zone savy

Question

Our current app stores all dates via the server's datetime. I'm thinking we need to update all datetime values in the database over to UTC time.

Now for displaying these dates back to the user, I know .Net 3.5 has datatypes that are specific to date time offsets. But does anyone see anything wrong with setting an application variable to represent the desired time zone for the site, and then do a dateadd with that offset to display times back to the user? For instance the Eastern Time zone would have a value of "-5".

Answer 1

Have a look at Coding Best Practices using DateTime in the .NET Framework. It isn't a tough read and you'll thank yourself for implementing them this way down the road - for example, you won't have to deal with daylight savings changes if you use .NET's provided timezone handling, for example.

Answer 2

Sounds like to you want to re-invent the wheel. If the application is a web app or geographically distributed, you need to know the time zone of the users. Knowing the server's correct time may not be helpful to them.

See this question for a code example:

http://stackoverflow.com/questions/893689/how-to-render-local-time-given-utc-datetime-values-in-asp-net-without-using-javas

Answer 3

Well, as long as you handle daylight savings "properly". Other than that, if your DB has anything to do with web, records should definitively store UTC. UTC does not "jump" on daylight savings time, so there is no trouble with sorting records entered during time change and datediff calculus is always ok.

Answer 4

In the latest version of .NET you get the TimeZoneInfo class that helps with converting dates.

You store the dates in GMT, also store the user's time zone, and then convert the dates each time you need to display them. The hard part is dealing with daylight savings time. The TimeZoneInfo class should help with this. Before that class was available you had to do it all yourself, storing tables of data on time zones and the date ranges of daylight savings time in each time zone.