Resolve ambiguous times for the stored date values inside a database

Question

In the UK, the clocks went back 1 hour on the 26th of October, 2014 at 02:00 AM. In this case, everybody in the UK observed the time between 01:00 to 01:59 twice at that day.

Assume that I am have a .NET software application where the date and time is very important on the specific time zone. In this case, what should I infer when I see 26th of October 01:00 inside my data storage system? Should I be storing the time zone such as BST and GMT, or should I be storing the offset values instead?

I am generally confused about what to do in this cases (hopefully I'm not the only confused person :D). The reason is the fact that a time zone abbreviation can have multiple meanings like BST:

• Bangladesh Standard Time (Standard Time)
• British Summer Time / British Daylight Time (Daylight Saving Time)

What is the best practice for these cases especially in .NET applications (both BCL DateTime value types and NodaTime solutions would be appreciated)?

Edit 1

There are a few suggestions for persisting in UTC. However, imagine that, I need to know about time zone in this application. An example would be flight ticket application. You would take off from one time zone and land in on another. Even if we can get away with converting the date and time to UTC and storing the time zone nearby, I'm still unable to find the proper solution for ambiguous times which is the whole point of this question. The below perfectly works (BCL value types, not NodaTime):

    static void ConvertToAndFromUtcCorrectly()
    {
        const string ukTimeZoneId = "GMT Standard Time";
        TimeZoneInfo ukTimeZone = TimeZoneInfo.FindSystemTimeZoneById(ukTimeZoneId);

        // 3/30/2014 2:00:00 AM (BST)
        DateTime aDateTimeInDst = new DateTime(2014, 3, 30, 2, 0, 0);

        // 3/30/2014 1:00:00 AM (UTC)
        DateTime aDateTimeInDstUtc = TimeZoneInfo.ConvertTimeToUtc(aDateTimeInDst, ukTimeZone);

        // 3/30/2014 2:00:00 AM (BST)
        DateTime backToUkTime = TimeZoneInfo.ConvertTimeFromUtc(aDateTimeInDstUtc, ukTimeZone);
    }

The below one fails miserably:

    static void ConvertToAndFromUtcWrongly()
    {
        const string ukTimeZoneId = "GMT Standard Time";
        TimeZoneInfo ukTimeZone = TimeZoneInfo.FindSystemTimeZoneById(ukTimeZoneId);

        // 10/26/2014 1:00:00 AM (This is meant to be BST, not GMT)
        DateTime ambiguousUkDateTime = new DateTime(2014, 10, 26, 1, 0, 0);

        // 10/26/2014 1:00:00 AM (UTC)
        DateTime ambiguousUkDateTimeUtc = TimeZoneInfo.ConvertTimeToUtc(ambiguousUkDateTime, ukTimeZone);

        // 10/26/2014 1:00:00 AM (WHAT?)
        DateTime backToUkTime = TimeZoneInfo.ConvertTimeFromUtc(ambiguousUkDateTimeUtc, ukTimeZone);
    }

Edit 2

In order to be more specific with my question here: According to my above sample, how would you store 26th of October, 2014 01:00 AM (which is an ambiguous time) in a data storage system and read/write this data in a .NET application?

Answer 1

How you should store the time depends very much on the context. In particular, storing the time something occurred (past or present tense) is a different problem than storing the time something will or should occur (future tense).

Past / Present

For past or present events, you can choose between a DateTimeOffset, or a UTC-based DateTime. While both represent a specific unambiguous point in time, the DateTimeOffset will also track the local time value. This is good for knowing things like whether it was morning or evening where the the timestamp was recorded. If you don't care about such things, use a UTC-based DateTime. (More on this in DateTime vs DateTimeOffset)

The adage "Persist globally, Display locally" applies well with past/present events. You can convert the timestamp to the local time zone of the user, or you can convert it to any time zone you want. This is helpful when the context of the viewer is not necessarily the same as the context where the time was recorded.

Using your airline example:

• You could record the time that the flight took off from London as a DateTimeOffset of 2014-10-26T01:00:00+01:00. As you pointed out, the one o'clock hour repeats on this day due to the fall-back transition. But since we recorded the offset, we know that this time was the first occurrence of 01:00.

• We could also record this same moment as a UTC-based DateTime of 2014-10-26T00:00:00Z. The only information lost is the actual local time of day.

• If we know (separately) that the data originated from the "Europe/London" time zone (or using the ID "GMT Standard Time" if you're using TimeZoneInfo), then we could affirmately say that this time was in BST (UTC+01:00) and not in GMT (UTC+00:00).

• Say the flight lasts for 7 hours and lands in New York. That would be 2014-10-26T07:00:00Z. It could also be expressed with the DateTimeOffset of 2014-10-26T08:00:00+01:00, but that offset isn't appropriate for New York. So we apply the destination time zone of "America/New_York" (or the ID "Eastern Standard Time" with TimeZoneInfo) and get 2014-10-26T03:00:00-04:00Z. You now know the flight lands at 3:00 AM local time in New York.

Future

Future events are much more complicated for several reasons:

• You don't necessarily know what the offset will be. You only know what you think it will be, based on the currently known time zone rules. Some governments change their time zone offsets or DST rules with very short notice, which doesn't always allow time for systems to be updated. In scheduling systems, staying on top of time zone updates is absolutely critical, and easily overlooked.

• Most human-driven events cannot be scheduled by UTC - especially recurring events. Imagine a daily alarm clock to wake you up at 7:00 every morning. If you schedule by UTC, then after a transition it would start going off at 6:00 or 8:00 (depending on when you made the original conversion and whether you are dealing with the spring or fall transition).

  • There is an exception to this. If the event follows a rule such as "every x hours" (or smaller), then you could schedule by UTC without issue. Be careful though, rules like "every x hours on Wednesday" don't qualify for this exception, because even determining whether or not it is a Wednesday involves time zones.
    
    

• The best approach is to schedule future events by the local time where the event applies. For this, you can use a DateTime that has DateTimeKind.Unspecified.

• In the alarm clock example, time zone is not important because you would use whatever local time zone was currently in effect (i.e., TimeZoneInfo.Local). But in the airline example, you absolutely need the originating and destination time zones. You should store the full ID of the time zone in the database as a string. Keep in mind that TimeZoneInfo uses Windows IDs, and identifiers like "Eastern Standard Time" represent both EST and EDT.

• You then need a way to disambiguate in the case of the ambiguity created by daylight saving time fall-back transitions, and to handle the gap created by spring-forward transitions. These are usually done algorithmically, or by policy.

  • For example, it would be normal for an alarm clock to use the first occurrence (the daylight occurrence) during fall-back, and to skip over any invalid values created by the spring-forward gap.

  • In the airline example, it might be necessary to store an extra boolean or bit that says which way it is scheduled. Alternatively, you might have some algorithm that works out an ambiguous departure time based on the estimated flight time and scheduled arrival time.

  • Then again, think about the human factor. What would passengers do if the plane was schedule to depart at 1:00 AM and then they found out it was the second instance. They probably wouldn't be too happy to sit around the airport and wait. Nor would they be happy if they thought it was the second instance and it turned out to be the first so they missed their flight. I will take a wild guess that many airlines would want to avoid scheduling departure times into a fall-back transition just to prevent unhappy customers.

So just to recap - think about the context. It really does matter, and there is not a single one-way that things should be done.

Answer 2

You can use UTC times in your application. The UTC time doesn't have daylight savings time, so it's completely linear.

By storing the time as UTC, your application will always know exactly what the time means. You can convert the time to local time to display it to users, which naturally means that they would see the gaps or overlaps in time when daylight savings time changes, but behind the scenes your application still knows exactly what the time is.

Answer 3

How about this for falling back:

The first occurrence of 1:00 A.M. through 1:59 A.M. will still be the same while the second occurrence of 1:00 A.M. through 1:59 A.M. can be reassigned as 13:00 P.M. through 13:59 P.M. (quasi-P.M. time) for regular time. In military time, the second occurrence of 0100 through 0159 can be reassigned as 2400 through 2459. So in regular time, you go from 13:59 P.M. to 2:00 A.M. while going from 2459 to 0200 in military time.