Convert date/times stored in one time zone to a user's local date/time

Question

I am dealing with a database in which all dates were incorrectly stored using the web servers local date/time (in this case, Eastern time.) Assuming that all date/times persisted in the database are Eastern, how can I convert this date to the users local time once I've selected the data into my C# models?

Answer 1

You might use TimeZoneInfo classes for a such a operation (from client side)

TimeZoneInfo.ConvertTime

Converts a time from one time zone to another.

  String dbTime = "2015.02.21 1:00 AM"; // <= A sample time

  DateTime time = DateTime.Parse(dbTime); // <= Convert it to a datetime

  TimeZoneInfo eastern = TimeZoneInfo.FindSystemTimeZoneById("Eastern Standard Time");  // <= TimeZoneInfo for Eastern
  TimeZoneInfo local = TimeZoneInfo.Local; // <=TimeZoneInfor for local

  Console.WriteLine(TimeZoneInfo.ConvertTime(time, estern, local)); // Will return the Local time related to provided to Eastern Time

Additional : As Matt Johnson explains , yes local will be server's time zone. So to use this approach as he Matt Johnson explained you need to identify the users timezone from a setting previously set, or from their IP (if possible) OR via a field passed to server (may be Through URL)

Answer 2

You can either store UTC-based datetimes in the database, or you can store a datetimeoffset with the correct offset. See DateTime vs DateTimeOffset to decide which makes sense for your use case. (I'll assume a UTC datetime for the rest of this response)

The problem is, since you've been only storing a datetime, and it's based on US Eastern Time, you already have a potential loss of information. Let me explain:

• Because of daylight saving time, the US Eastern time zone alternates between two different offsets. During most of the year, it's at UTC-5, but in the summer it switches to UTC-4.
• You'll need to take the date and time into account to calculate the correct offset to use.
• In the fall, when the clocks "fall-back", the local time jumps from 1:59:59.999 back to 1:00. This means that any values in the 1:00 hour on that particular date are ambiguous. If you didn't store anything else to help you correlate them, you may have lost data.

Now perhaps you will be lucky and find there are no transactions recorded in that small window, but perhaps not. There is one way that you might be able to disambiguate though.

• Many databases have an integer primary key that is auto-incrementing, or have some other incremental field. You can use this for disambiguation.

• For example:

  ID  | ... | TransactionDate
  ----+-----+--------------------
  101 | ... | 2014-11-02 00:55:00
  102 | ... | 2014-11-02 01:00:00
  103 | ... | 2014-11-02 01:30:00
  104 | ... | 2014-11-02 01:55:00
  105 | ... | 2014-11-02 01:15:00  <--- transition!
  106 | ... | 2014-11-02 01:59:00
  107 | ... | 2014-11-02 02:00:00
  

In the above table, because we have some records in the 1:00 hour that are out of sequence, it's a reasonable assumption that records 101-104 occurred during Eastern Daylight Time (UTC-4), and records 105-107 occurred during Eastern Standard Time (UTC-5).

This method is not something you can easily automate though. Sometimes you will have transactions in the ambiguous hour, but not any out of sequence. That means that either all of them came before the transition, or all of them came after. There's no way to tell.

You'll need to write some C# code or SQL scripts to query the table and transform the data. I recommend either creating a second table for the output, or a second column within the existing table. Don't try to update the values in place, or you will have a hard time unwinding it if things go wrong.

With regard to the conversion itself, if you are doing it in .NET code, you can use the TimeZoneInfo class, with the "Eastern Standard Time" time zone id. Don't get confused with the naming, that identifier represents both EST and EDT - despite the word "standard" in it's name.

DateTime dt = (DateTime) yourSqlDataReader["TransactionDate"];
TimeZoneInfo tz = TimeZoneInfo.FindSystemTimeZoneById("Eastern Standard Time");
if (tz.IsAmbiguousTime(dt))
{
    // record this item in some way, so you can review it manually later
}
else
{
    DateTime utc = TimeZoneInfo.ConvertTimeToUtc(dt, tz);
    // save this field to a new table or a new column in the current table
}

After you do the conversions, go though and manual fill in any gaps from the ambiguous periods. When you're satisfied, then copy the values back into place, or move your code to point at the new UTC column, etc.

From this point forward, always store transaction times in terms of UTC. In .NET, use DateTime.UtcNow, or in SQL, use GETUTCDATE() or SYSUTCDATETIME().

Now the last part of the problem is - how are you going to convert to the user's time zone for display? Well you can certainly use TimeZoneInfo again, or you can use a more comprehensive library, such as Noda Time. But whichever path you take, you'll probably need to know what the user's time zone is! The simplest thing to do is to gather this from the user of your application, via a dropdown setting in a user profile page (for example).

Another approach, if you are good with JavaScript, you can pass the UTC time all the way down to the client, and then load it into a JavaScript Date object, or you can use a JavaScript library like moment.js. The benefit of either of these is that the client will automatically be able to convert the value to whatever time zone the user's computer is set to. This works particularly well if you are using a modern application development style, such as sending data via WebAPI and displaying it with a JavaScript framework like Angular.