在遵守夏令时的同时解析本地时间(到 UTC)的有序时间戳 [英] Parsing of Ordered Timestamps in Local Time (to UTC) While Observing Daylight Saving Time

问题描述

我有 CSV 数据文件，其中包含本地时间的时间戳记录.不幸的是，数据文件涵盖了夏令时更改的时期(2013 年 11 月 3 日)，因此记录的时间戳的时间部分为:12:45, 1:00, 1:15, 1:30, 1:45、1:00、1:15、1:30、1:45、2:00.我希望能够将值转换为 UTC 并将其存储在数据库中.

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.

不幸的是，.NET 的标准 DateTime.Parse() 函数将解析为(所有 2013 年 11 月 3 日):

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

所以它永远不会看到 1:00-1:59:59 am 范围在 DST 中，并且我在 UTC 中解析的时间戳跳了一个小时.

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.

是否有库或类可以让我解析时间戳并考虑 DST 的变化?就像某些可实例化的类会记住它已经收到的时间戳流并相应地调整解析的时间戳?

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. 我在文件的标题部分有本地和 UTC 格式的文件开始时间(第一条记录的时间戳).
2. 记录按时间戳排序
3. 所有当地时间均采用东部标准时间
4. 数据也可能以另一种方式:从夏令时进入数据
5. 记录包含完整的时间戳，格式为:yyyy/mm/dd HH:mm:ss (2013/11/03 00:45:00)

注意:虽然我的软件是用 C# 编写的，但我没有特别标记 C#/.NET，因为我认为我可以使用任何语言的解决方案实现并在必要时重新编码.

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.

推荐答案

在 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);
}

输出:

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

请注意，这假设您第一次遇到像 1:00 这样的模糊时间，它将处于夏令时.假设您的列表被截断为最后 5 个条目 - 您不会知道那些是在标准时间.在这种特殊情况下，您无能为力.

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.

这篇关于在遵守夏令时的同时解析本地时间(到 UTC)的有序时间戳的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！