Display DateTime data in users TimeZone in DataTable

Question

We have a reporting application where all DateTimes are stored in UTC in the database (SQL server 2005)

The reporting application works simply be retrieving a DataTable and binding to a DataGrid displaying the information.

If we know the logged in users time zone (e.g. +3 GMT), is there any way to update the DataTables dates which are in UTC to now display in the users time zone?

We know we can spool thorugh each column/row in the DataTable and do a conversion but is there a better more efficient way?

Any help will be greatly appreciated! Billy Stack

Answer 1

I would modify the time as it is displayed to the user. Keep how it is stored consistent, but also store what their timezone is, and then modify the UI to show the correct local time. For example, if their time zone is Central Standard Time, then you could use something similar to the following code:

SomeDataObject someDataObject = new SomeDataObject();

someDataObject.TimeZone = -5; //UTC Timezone for Central Standard Time
someDataObject.Time = DateTime.Now;

DateTime someTime = someDataObject.Time;
someTime.Add(someDataObject.TimeZone); // Display this back to the user

Answer 2

If using .NET, there's a built in class that was made precisely for this purpose. TimeZoneInfo (http://msdn.microsoft.com/en-us/library/system.timezoneinfo.aspx) as taken from the article:

A TimeZoneInfo object can represent any time zone, and methods of the TimeZoneInfo class can be used to convert the time in one time zone to the corresponding time in any other time zone. The members of the TimeZoneInfo class support the following operations:

• Retrieving a time zone that is already defined by the operating system.
• Enumerating the time zones that are available on a system.
• Converting times between different time zones.
• Creating a new time zone that is not already defined by the operating system.
• Serializing a time zone for later retrieval.

Hope this helps.