Best way to handle dates with respect to time zones in .Net

Question

I have lately been messing around with time zones in a web application that supports .net time zone. I developed the following solution for processing time zones.

My decision:

• User profiles keep the time zone in which they are located.
• On the web server, do all conversions to and from UTC.
• Save dates in the database in UTC format.

My questions:

• Do you guys think this is the best approach in .NET?
• Am I using datetime2 in UTC well enough or should I store clients time using an offset in the database (mostly 10-10-2012 4:00:00 - 10-10-2012 00:00:00 4:00)?
• As an aside, some of you will probably notice that when hops through DST are processed in server code, transferring an offset to SP or the like in the database will not correctly handle DST. Any ideas on this?

Here is a sample code for converting time.

private TimeZoneInfo GetTimeZoneInfo() { var timeZone = TimeZoneDropdown.SelectedValue; switch (timeZone) { case "Eastern": return TimeZoneInfo.FindSystemTimeZoneById("Eastern Standard Time"); case "Central": return TimeZoneInfo.FindSystemTimeZoneById("Central Standard Time"); case "Mountain": return TimeZoneInfo.FindSystemTimeZoneById("Mountain Standard Time"); case "Pacific": return TimeZoneInfo.FindSystemTimeZoneById("Pacific Standard Time"); case "Alaskan": return TimeZoneInfo.FindSystemTimeZoneById("Alaskan Standard Time"); } return TimeZoneInfo.FindSystemTimeZoneById("Eastern Standard Time"); } public DateTime ConvertLocalDateTimeToUtc(DateTime dateTime) { var timeZone = GetTimeZoneInfo(); return TimeZoneInfo.ConvertTimeToUtc(DateTime.SpecifyKind(dateTime, DateTimeKind.Unspecified), timeZone); } public DateTime ConvertUtcToLocalDateTime(DateTime dateTime) { var timeZone = GetTimeZoneInfo(); return TimeZoneInfo.ConvertTimeFromUtc(DateTime.SpecifyKind(dateTime, DateTimeKind.Utc), timeZone); } 

Answer 1

Your current approach is not wrong, but you can do it better by tracking offsets.

When you talk about offsets, it seems you think of them as related to the time zone. But understand that most time zones have two different offsets: one for standard time and one for daylight. Microsoft's timezone identifier for both still has a “Standard” in the string, so this can be part of the confusion. But the TimeZoneInfo you work with does indeed track both standard and daily shifts.

You need to associate the offset with each individual date and time. And you do this using the DateTimeOffset class in .Net and the DateTimeOffset data type in SQL Server.

If you use them consistently, then you need to transfer to UTC and from UTC.