Datetime localization

Question

Hi,

My database is located in e.g. california. My user table has all the user's timezone e.g. -0700 UTC

How can I adjust the time from my database server whenever I display a date to the user who lives in e.g. new york? UTC/GMT -4 hours

Answer 1

You should store your data in UTC format and showing it in local timezone format.

DateTime.ToUniversalTime() -> server;
DateTime.ToLocalTime() -> client

You can adjust date/time using AddXXX methods group, but it can be error prone. .NET has support for time zones in System.TimeZoneInfo class.

Answer 2

Up until .NET 3.5 (VS 2008), .NET does not have any built-in support for timezones, apart from converting to and from UTC.

If the time difference is always exactly 3 hours all year long (summer and winter), simply use yourDate.AddHours(3) to change it one way, and yourDate.AddHours(-3) to change it back. Be sure to factor this out into a function explaining the reason for adding/substracting these 3 hours.

Answer 3

You could use a combination of TimeZoneInfo.GetSystemTimeZones() and then use the TimeZoneInfo.BaseUtcOffset property to offset the time in the database based on the offset difference

Info on System.TimeZoneInfo here

Answer 4

Have you looked at SQL's GETUTCDATE() function? That will give the server UTC time, then use DATEADD() to adjust using your timezone.

Answer 5

You know, this is a good question. This year I've done my first DB application and as my input data related to time is an Int64 value, that is what I stored off in the DB. My client applications retrieve it and do DateTime.FromUTC() or FromFileTimeUTC() on that value and do a .LocalTime() to show things in their local time. I've wondered whether this was good/bad/terrible but it has worked well enough for my needs thus far. Of course the work ends up being done by a data access layer library I wrote and not in the DB itself.

Seems to work well enough, but I trust others who have more experience with this sort of thing could point out where this is not the best approach.

Good Luck!

Answer 6

If you use .Net, you can use TimeZoneInfo. Since you tagged the question with 'c#', I'll assume you do.

The first step is getting the TimeZoneInfo for the time zone in want to convert to. In your example, NY's time zone. Here's a way you can do it:

//This will get EST time zone
TimeZoneInfo clientTimeZone
    = TimeZoneInfo.FindSystemTimeZoneById("Eastern Standard Time");

//This will get the local time zone, might be useful
// if your application is a fat client
TimeZoneInfo clientTimeZone = TimeZoneInfo.Local;

Then, after you read a DateTime from your DB, you need to make sure its Kind is correctly set. Supposing the `DateTime's in the DB are in UTC (by the way, that's usually recommended), you can prepare it to be converted like this:

DateTime aDateTime = dataBaseSource.ReadADateTime();
DateTime utcDateTime = DateTime.SpecifyKind(aDateTime, DateTimeKind.Utc);

Finally, in order to convert to a different time zone, simply do this:

DateTime clientTime = TimeZoneInfo.ConvertTime(utcDateTime, clientTimeZone);

Some extra remarks:

• TimeZoneInfo can be stored in static fields, if you are only interested in a few specific time zones;
• TimeZoneInfo` store information about daylight saving. So, you wouldn't have to worry about that;
• If your application is web, finding out in which time zone your client is in might be hard. One way is explained here: http://kohari.org/2009/06/15/automagic-time-localization/

I hope this helps. :)