time - c# daylight savings duplicate hour convert to UTC

Question

I am using TimeZoneInfo to convert between client side wallclock 'Eastern Time' and UTC. My problem is with the 'duplicate' hour that occurs during autumn DST change.

During conversion from UTC to Eastern:
2010-11-07 06:00 UTC --> gives 2010-11-07T01:00:00-03:30
2010-11-07 07:00 UTC --> gives 2010-11-07T01:00:00-03:30
How can I know which is first hour and which is second? DateTime.IsDaylightSavingTime() returns false for both hours, but shouldn't it return true for the first hour?

Likewise, how do I store 2010-11-07 01:00:00-03:30? How can my app convert to UTC since it could be 2010-11-07 06:00 or 2010-11-07 07:00

For those who need code, I am cycling through a datatable with UTC datetime column, trying to convert to Eastern with a 'DupHr' column for the second duplicate hour, but I always end up with both 01:00 hours having 'DupHr' = 1.

    TimeZoneInfo est = TimeZoneInfo.FindSystemTimeZoneById("Eastern Standard Time");

    DateTime EasternTime;
    DateTime DuplicateHour = new DateTime(2010, 11, 7, 1, 0, 0);  // hard coded for this example
    TimeZoneInfo.AdjustmentRule[] rules =  est.GetAdjustmentRules();

    foreach (DataRow row in dt.Rows)
    {
        row["DupHr"] = 0;  // by default not duplicate hour
        EasternTime = TimeZoneInfo.ConvertTimeFromUtc((DateTime)row[UTCColumnName], est);
        if (!EasternTime.IsDaylightSavingTime())
        {
            if (EasternTime.Equals(DuplicateHour ))
            {
                row["DupHr"] = 1;   // This is the second duplicate hour !
            }
        } else

            EasternTime.Add(rules[1].DaylightDelta);  // Add DST offset from rule #1

        row[newESTColumnName] = EasternTime;        
    }

THANKS!

Solution

It is important to know more than 'ambiguous' for the duplicate hours. The hours must be unique (first and second). Consider a toll booth money counter application which must operate 24x7 and totalize each hour's collection. Money collected in each hour must be identifiable. First hour 1:00 to 1:59 is different from second 1:00 to 1:59 hour. The routine below isSecondHour will return true only if the passed time is in second hour of autumn DST change. The user interface can display this flag appropriately.

 // Get the DST rule for the year and zone  (rules may change from year to year as in 2004)
    public static TimeZoneInfo.AdjustmentRule GetDSTrule(int Year, TimeZoneInfo zone)
    {
        TimeZoneInfo.AdjustmentRule[] rules = zone.GetAdjustmentRules();

        foreach (TimeZoneInfo.AdjustmentRule rul in rules)
        {
            if (rul.DateStart < new DateTime(Year, 1, 1) && rul.DateEnd > new DateTime(Year, 1, 1))
            {
                return rul;
            }
        }
        return null;
    }

    // Determine if 'localtime' is in the second duplicate DST hour.
    public static Boolean isSecondHour(TimeZoneInfo localzone, DateTime localtime, DateTime UTCtime)
    {

        if (localzone.IsAmbiguousTime(localtime))
        {
            TimeZoneInfo.AdjustmentRule rul = GetDSTrule(localtime.Year, localzone);
            return UTCtime.Add(localzone.GetUtcOffset(localtime)) == localtime;
        }
        else
            return false;
    }


    static void Main(string[] args)
    {
        var est = TimeZoneInfo.FindSystemTimeZoneById("Eastern Standard Time");

        var times = new DateTime[] {
        new DateTime (2010, 11, 7, 3,  0, 0, DateTimeKind.Unspecified),
        new DateTime (2010, 11, 7, 4,  0, 0, DateTimeKind.Unspecified),
        new DateTime (2010, 11, 7, 5,  0, 0, DateTimeKind.Unspecified),
        new DateTime (2010, 11, 7, 5,  30, 0, DateTimeKind.Unspecified),
        new DateTime (2010, 11, 7, 6,  0, 0, DateTimeKind.Unspecified),
        new DateTime (2010, 11, 7, 6,  30, 0, DateTimeKind.Unspecified),
        new DateTime (2010, 11, 7, 7,  0, 0, DateTimeKind.Unspecified),
        new DateTime (2010, 11, 7, 8,  0, 0, DateTimeKind.Unspecified)

    };

    DateTime EasternTime;
    Console.WriteLine("UTC Time  |  Est Time   | IsDaylightSaving | IsAmbiguousTime | isSecondHour ");

    foreach (var utc in times)
    {
        // Get Eastern Time from UTC using standard convert routine.
        EasternTime = TimeZoneInfo.ConvertTimeFromUtc(utc, est);
        Console.WriteLine("{0:HH:mm}     |   {1:HH:mm}     | {2,11}      |      {3,5}      |      {4,5}", utc,EasternTime, est.IsDaylightSavingTime(EasternTime), est.IsAmbiguousTime(EasternTime),isSecondHour(est,EasternTime, utc));
     }

results

UTC Time  |  Est Time   | IsDaylightSaving | IsAmbiguousTime | isSecondHour
03:00     |   23:00     |        True      |      False      |      False
04:00     |   00:00     |        True      |      False      |      False
05:00     |   01:00     |       False      |       True      |      False
05:30     |   01:30     |       False      |       True      |      False
06:00     |   01:00     |       False      |       True      |       True
06:30     |   01:30     |       False      |       True      |       True
07:00     |   02:00     |       False      |      False      |      False
08:00     |   03:00     |       False      |      False      |      False

Answer 1

Summary

You can't know, because by not storing the offset you've lost an important piece of information, the time zone the time was originally in, which as you've pointed out could be either Eastern Standard Time or Eastern Daylight Time.

Detecting the ambiguous time

TimeZoneInfo provides the method IsAmbiguousTime to check if this might be the case.

The problem with your detection for this ambiguous time is you're trying to use IsDaylightSavings which returns false for ambiguous times, as illustrated by this example:

var est = TimeZoneInfo.FindSystemTimeZoneById("Eastern Standard Time");

var times = new DateTime[] {
    new DateTime (2010, 11, 7, 4,  0, 0, DateTimeKind.Utc),
    new DateTime (2010, 11, 7, 5,  0, 0, DateTimeKind.Utc),
    new DateTime (2010, 11, 7, 5, 30, 0, DateTimeKind.Utc),
    new DateTime (2010, 11, 7, 6,  0, 0, DateTimeKind.Utc),
};

Console.WriteLine("     Time  | IsDaylightSaving | IsAmbiguousTime");
foreach (var t in times) {
    var time = TimeZoneInfo.ConvertTimeFromUtc(t, est);
    Console.WriteLine ("    {0:HH:mm}  | {1,11}      |      {2,5}", time, est.IsDaylightSavingTime(time), est.IsAmbiguousTime(time));
}

Result:

 Time  | IsDaylightSaving | IsAmbiguousTime
00:00  |        True      |      False
01:00  |       False      |       True
01:30  |       False      |       True
01:00  |       False      |       True

So you want to be using est.IsAmbiguousTime(EasternTime). Then there is no need for DuplicateHour as this will cover the full time range that is ambiguous on that day. DateTimeOffset does not suffer this problem due to it explicitly storing the offset.

Converting EST to UTC and storing in the database

For your initial conversion from EST to UTC of the existing data in the database will want to store the offset for future use. For non-ambiguous times this can be retrieved from the timezone. However, as you have identified, for ambiguous times this information will not be available. For these times you will have to assume which offset to use and flag the time in the DB as suspect so the UI can react accordingly when displaying these times.

Depending on how much data is affected it might not be worth the effort of changing the UI and to simply ignore the problem, especially if it really isn't that important to a user if the time is out by an hour (since on the user's screen in that timezone it would still display as 1am). The DB will still record that the time was suspect if you ever later change your mind.

Converting from UTC to EST and detecting ambiguous times

Firstly, use DateTimeOffset as this can tell the difference between 1am EST, and 1am EDT. At which point TimeZoneInfo.IsAmbiguousTime(DateTimeOffset) can be used to highlight the duplicate times on screen, and TimeZoneInfo.IsDaylightSavings(DateTimeOffset) will also correctly return true or false.

var est = TimeZoneInfo.FindSystemTimeZoneById("Eastern Standard Time");

var times = new DateTimeOffset[] {
    new DateTimeOffset (2010, 11, 7, 4, 00, 0, TimeSpan.Zero),
    new DateTimeOffset (2010, 11, 7, 5, 00, 0, TimeSpan.Zero),
    new DateTimeOffset (2010, 11, 7, 5, 30, 0, TimeSpan.Zero),
    new DateTimeOffset (2010, 11, 7, 6, 00, 0, TimeSpan.Zero),
};

Console.WriteLine("     Time  | IsDaylightSaving | IsAmbiguousTime");
foreach (var t in times) {
    var time = TimeZoneInfo.ConvertTime (t, est);
    Console.WriteLine ("    {0:HH:mm}  | {1,11}      |      {2,5}", time, est.IsDaylightSavingTime(time), est.IsAmbiguousTime(time));
}

Result:

 Time  | IsDaylightSaving | IsAmbiguousTime
00:00  |        True      |      False
01:00  |        True      |       True
01:30  |        True      |       True
01:00  |       False      |       True

Future Considerations

User interface issues

When displaying to a user, it shouldn't matter if the local time is ambiguous or not (duplicate hour). You can simply convert the UTC time to their timezone and format as a string. You might want to check IsAmbiguousTime to display a hint to the user why they might be seeing "1am" twice. Sorting the information by date should be done using UTC. Going from UTC to a local time should never be ambiguous, since each point in time only exists once in UTC, there are no duplicate hours.

So the only problem now, is if the user is entering a time and you need to interpret what time they meant, as the user is unlikely to enter an offset or even care about such details. Sadly there is no simple way of handling this and short of trying to teach your users about offsets they will make mistakes and enter the wrong time. Eg they might enter 4am thinking 4 hours past midnight, forgetting that on that night there is 1 hour extra/less. Alternatively they could enter 3am on a day when the clocks go forwards at 3am, which on that day is a time that simply doesn't exist.

Fortunately the time the clocks changed is aimed to minimize the user input problem as most people are asleep. So the system could take a best guess and accept being out by an hour sometimes. If it really matters then you could check if that day has daylight savings and show a different UI with a warning/prompt.

Storage and transfer

If storing the time in MSSQL server the datetimeoffset should be preferred as this can handle storing both the time and offset. When using this type MSSQL server can handle comparing times with different offsets correctly.

For databases that do not support such a type, you can store the time in UTC in the database, and store the offset for that time in a separate column. This will allow you to accurately know the local time it was recorded at.

When exchanging with external systems, then ideally transfer the time as local in format yyyy-MM-dd HH:mm:sszzzz (eg 2010-11-07 01:00:00-03:30) so that both the time and offset can be preserved. Otherwise UTC is normally the best choice but ideally should have be suffixed with 'Z' or '+00:00' to make this obvious.

In memory, the DateTimeOffset class is a better choice because it can represent any arbitary offset compared to DateTime which can only represent UTC or the system's local time.

---

Note, the accuracy of TimeZoneInfo's daylight savings depends on OS version, service packs and windows updates applied.

Also, it matters how daylights savings is applied. If they are applied by the OS using the "Automatically adjust clock for daylight savings" then the offset will be adjusted correctly. If the admin has disabled this and manually adjusts the time by adding/subtracting an hour then the OS will be unaware of this and will be operating with the wrong offset. See, TimeZoneInfo.Local for other notes regarding this OS setting.