Search code examples
datetimetimestampdstdate-parsing

Parsing of Ordered Timestamps in Local Time (to UTC) While Observing Daylight Saving Time

I have CSV data files with timestamped records that are in local time. Unfortunately the data files cover the period where daylight saving time changes (Nov 3rd 2013) so the time component of the timestamps for the records go: 12:45, 1:00, 1:15, 1:30, 1:45, 1:00, 1:15, 1:30, 1:45, 2:00. I want to be able to convert and store the values in the database as UTC.

Unfortunately the standard DateTime.Parse() function of .NET will parse as this (all November 3rd 2013):

| Time String | Parsed Local Time | In DST | Parsed Local Time to UTC
|  12:45 am   |    12:45 am       |   Yes  |     4:45 am
| 12:59:59 am |    12:59:59 am    |   Yes  |     4:59:59 am
|  01:00 am   |     1:00 am       |   No   |     6:00 am
|  01:15 am   |     1:15 am       |   No   |     6:15 am

So it never sees the 1:00-1:59:59 am range as being in DST and my parsed timestamps in UTC jumps an hour.

Is there a library or class out there that will allow me to parse the timestamps and take into account the change in DST? Like some instantiatable class that will remember the stream of timestamps it's already received and adjust the parsed timestamp accordingly?

Assumptions about the data that can be made while parsing:

  1. I have the start time of file (timestamp of the first record) in the header section of the file in both local and UTC.
  2. The records are in order by timestamp
  3. All local times are in Eastern Standard
  4. The data could also go the other way: from out of DST into it
  5. The records contain a full timestamp in the format: yyyy/mm/dd HH:mm:ss (2013/11/03 00:45:00)

Note: While my software is in C#, I did not tag C#/.NET specifically as I figured I could use any language's implementation of a solution and recode if necessary.

Solution

  • In C#:

    // Define the input values.
string[] input =
{
    "2013-11-03 00:45:00",
    "2013-11-03 01:00:00",
    "2013-11-03 01:15:00",
    "2013-11-03 01:30:00",
    "2013-11-03 01:45:00",
    "2013-11-03 01:00:00",
    "2013-11-03 01:15:00",
    "2013-11-03 01:30:00",
    "2013-11-03 01:45:00",
    "2013-11-03 02:00:00",
};

// Get the time zone the input is meant to be interpreted in.
TimeZoneInfo tz = TimeZoneInfo.FindSystemTimeZoneById("Eastern Standard Time");

// Create an array for the output values
DateTimeOffset[] output = new DateTimeOffset[input.Length];

// Start with the assumption that DST is active, as ambiguities occur when moving
// out of daylight time into standard time.
bool dst = true;

// Iterate through the input.
for (int i = 0; i < input.Length; i++)
{
    // Parse the input string as a DateTime with Unspecified kind
    DateTime dt = DateTime.ParseExact(input[i], "yyyy-MM-dd HH:mm:ss",
                                      CultureInfo.InvariantCulture);

    // Determine the offset.
    TimeSpan offset;
    if (tz.IsAmbiguousTime(dt))
    {
        // Get the possible offsets, and use the DST flag and the previous entry
        // to determine if we are past the transition point.  This only works
        // because we have outside knowledge that the items are in sequence.
        TimeSpan[] offsets = tz.GetAmbiguousTimeOffsets(dt);
        offset = dst && (i == 0 || dt >= output[i - 1].DateTime)
                 ? offsets[1] : offsets[0];
    }
    else
    {
        // The value is unambiguous, so just get the single offset it can be.
        offset = tz.GetUtcOffset(dt);
    }

    // Use the determined values to construct a DateTimeOffset
    DateTimeOffset dto = new DateTimeOffset(dt, offset);

    // We can unambiguously check a DateTimeOffset for daylight saving time,
    // which sets up the DST flag for the next iteration.
    dst = tz.IsDaylightSavingTime(dto);

    // Save the DateTimeOffset to the output array.
    output[i] = dto;
}


// Show the output for debugging
foreach (var dto in output)
{
    Console.WriteLine("{0:yyyy-MM-dd HH:mm:ss zzzz} => {1:yyyy-MM-dd HH:mm:ss} UTC",
                       dto, dto.UtcDateTime);
}

    Output:

    2013-11-03 00:45:00 -04:00 => 2013-11-03 04:45:00 UTC
2013-11-03 01:00:00 -04:00 => 2013-11-03 05:00:00 UTC
2013-11-03 01:15:00 -04:00 => 2013-11-03 05:15:00 UTC
2013-11-03 01:30:00 -04:00 => 2013-11-03 05:30:00 UTC
2013-11-03 01:45:00 -04:00 => 2013-11-03 05:45:00 UTC
2013-11-03 01:00:00 -05:00 => 2013-11-03 06:00:00 UTC
2013-11-03 01:15:00 -05:00 => 2013-11-03 06:15:00 UTC
2013-11-03 01:30:00 -05:00 => 2013-11-03 06:30:00 UTC
2013-11-03 01:45:00 -05:00 => 2013-11-03 06:45:00 UTC
2013-11-03 02:00:00 -05:00 => 2013-11-03 07:00:00 UTC

    Note that this assumes that the first time you encounter an ambiguous time like 1:00 that it will be in DST. Say your list was truncated to just the last 5 entries - you wouldn't know that those were in standard time. There's not much you could do in that particular case.