Storing date/times as UTC in database

Posted by James on Stack Overflow See other posts from Stack Overflow or by James
Published on 2010-04-05T19:07:28Z Indexed on 2010/04/06 12:43 UTC
Read the original article Hit count: 262

Filed under:
|
|
|

I am storing date/times in the database as UTC and computing them inside my application back to local time based on the specific timezone. Say for example I have the following date/time:

01/04/2010 00:00

Say it is for a country e.g. UK which observes DST (Daylight Savings Time) and at this particular time we are in daylight savings. When I convert this date to UTC and store it in the database it is actually stored as:

31/03/2010 23:00

As the date would be adjusted -1 hours for DST. This works fine when your observing DST at time of submission. However, what happens when the clock is adjusted back? When I pull that date from the database and convert it to local time that particular datetime would be seen as 31/03/2009 23:00 when in reality it was processed as 01/04/2010 00:00.

Correct me if I am wrong but isn't this a bit of a flaw when storing times as UTC?

Example of Timezone conversion

Basically what I am doing is storing the date/times of when information is being submitted to my system in order to allow users to do a range report. Here is how I am storing the date/times:

public DateTime LocalDateTime(string timeZoneId)
{
    var tzi = TimeZoneInfo.FindSystemTimeZoneById(timeZoneId);
    return TimeZoneInfo.ConvertTimeFromUtc(DateTime.UtcNow, tzi).ToLocalTime(); 
}

Storing as UTC:

var localDateTime = LocalDateTime("AUS Eastern Standard Time");
WriteToDB(localDateTime.ToUniversalTime());

© Stack Overflow or respective owner

Related posts about c#

Related posts about utc