c# - Storing date/times as UTC in database

Question

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/2010 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).ToUniversalTime().ToLocalTime(); 
}

Storing as UTC:

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

Answer 1

You don't adjust the date for DST changes based on whether you're currently observing them - you adjust it based on whether DST is observed at the instant you're describing. So in the case of January, you wouldn't apply the adjustment.

There is a problem, however - some local times are ambiguous. For example, 1:30am on October 31st 2010 in the UK can either represent UTC 01:30 or UTC 02:30, because the clocks go back from 2am to 1am. You can get from any instant represented in UTC to the local time which would be displayed at that instant, but the operation isn't reversible.

Likewise it's very possible for you to have a local time which never occurs - 1:30am on March 28th 2010 didn't happen in the UK, for example - because at 1am the clocks jumped forward to 2am.

The long and the short of it is that if you're trying to represent an instant in time, you can use UTC and get an unambiguous representation. If you're trying to represent a time in a particular time zone, you'll need the time zone itself (e.g. Europe/London) and either the UTC representation of the instant or the local date and time with the offset at that particular time (to disambiguate around DST transitions). Another alternative is to only store UTC and the offset from it; that allows you to tell the local time at that instant, but it means you can't predict what the local time would be a minute later, as you don't really know the time zone. (This is what DateTimeOffset stores, basically.)

We're hoping to make this reasonably easy to handle in Noda Time, but you'll still need to be aware of it as a possibility.

EDIT:

The code you've shown is incorrect. Here's why. I've changed the structure of the code to make it easier to see, but you'll see it's performing the same calls.

var tzi = TimeZoneInfo.FindSystemTimeZoneById("AUS Eastern Standard Time");
var aussieTime = TimeZoneInfo.ConvertTimeFromUtc(DateTime.UtcNow, tzi);
var serverLocalTime = aussieTime.ToLocalTime(); 
var utcTime = serverLocalTime.ToUniversalTime();

So, let's think about right now - which is 13:38 in my local time (UTC+1, in London), 12:38 UTC, 22:39 in Sydney.

Your code will give:

aussieTime = 22:39 (correct)
serverLocalTime = 23:39 (*not* correct)
utcTime = 22:39 (*not* correct)

You should not call ToLocalTime on the result of TimeZoneInfo.ConvertTimeFromUtc - it will assume that it's being called on a UTC DateTime (unless it's actually got a kind of DateTimeKind.Local, which it won't in this case).

So if you're accurately saving 22:39 in this case, you aren't accurately saving the current time in UTC.