sql-server - SQL 服务器 : convert common date column data to date with timezone

Question

我正在为此苦苦挣扎:我有一张名为 Foo 的表。与 datetime列(没有时区信息)。但是，我们正在进行这样的“国际化”工作，现在我们需要存储日期时间和时区信息(datetimeoffset 列类型)。

假设所有预先存在的日期时间数据使用 -03:00 (美国/圣保罗 - 巴西利亚)时区和 CONVERT(varchar(50), foo.date, 127)返回类似 2012-04-17T01:14:33 ，我应该如何继续获取 ISO 8601 日期时间字符串(类似于 2012-04-17T04:14:33Z 或 2012-04-17T01:14:33 -03:00 )？

Answer 1

首先，认识到巴西利亚和圣保罗都遵守 daylight saving time一年中的部分时间，所以它不仅仅是简单的转换为 -03:00 .如果它被固定到那个偏移量，你可以使用 TODATETIMEOFFSET功能 - 但你不能。有两种不同的偏移量需要考虑。 -03:00标准时间，-02:00当夏令时生效时。

确定 DST 何时在巴西生效并不是一件容易的事，因为有一段复杂的变化历史。当前规则可以找到here in the tzdb ，目前表示(自 2008 年以来)在 10 月的第 3 个星期日开始 DST，并在 2 月的第 3 个星期日结束 - 除非那一天恰好是 Carnival Sunday ，在这种情况下，DST 改为在 2 月的第 4 个星期日结束。

当然，您可以将这些规则编码到存储过程或用户定义的函数中，但这会非常复杂。

有两种更好的方法值得考虑:

您可以编写一个 SQL CLR 函数。如果这样做，您有两个子选项:

如果您要进行转换的服务器的本地时区设置为巴西利亚，那么您可以简单地使用 new DateTimeOffset(yourDateTime) - 这将根据本地时区选择适当的偏移量。

您可以使用 TimeZoneInfo类，但需要 Unsafe Mode被启用。如果这样做，转换将如下所示:

var tz = TimeZoneInfo.FindSystemTimeZoneById("E. South America Standard Time");
var dto = new DateTimeOffset(yourDateTime, tz.GetUtcOffset(yourDateTime));

你可以使用我的SQL Server Time Zone Support项目，它包含了所有正确的时区转换，并为您提供了一些简单的功能。

DECLARE @tz varchar(25)
SET @tz = 'America/Sao_Paulo';
SELECT Tzdb.SwitchZone(Tzdb.LocalToUtc(YourDateTime, @tz, 1, 1), @tz);

请注意，我首先将本地时间转换为 UTC，然后将其切换回原始时区。我会 probably update the project to include this as a single function ，但现在这两个都可以工作。

当然，对于这些选项中的任何一个，您都需要编写 UPDATE陈述。我建议创建一个新列，然后更新该列，然后仅在您对转换感到满意后删除原始列。不要尝试更改现有列的类型。或者，您可以创建一个新表并通过从一个表中选择并插入另一个表来进行转换。

最后，考虑到当时区有 DST 时，任何时候您从本地时间转换为瞬时时间点(无论是 UTC datetime 还是 datetimeoffset ) - 都可能存在歧义。这是因为“回退”转换在 DST 结束时创建了本地时间值的重叠。例如，在您所在的时区，DST 将在 2015-10-18 00:00:00 下一次开始。 , 并将在 2016-02-21 00:00:00 结束.当 DST 结束时，来自 2016-02-20 23:00:00 的值通过 2016-02-20 23:59:59.9999999是模棱两可的。它们的偏移量可能为 -02:00如果它们指的是在 DST 结束之前发生的事件，或者它们的偏移量可能为 -03:00如果他们指的是 DST 结束后发生的事件。

在我上面的建议中，建议#1 中的两个选项都将选择后面出现的情况，以防出现歧义。如果您使用建议 #2，您可以使用 LocalToUtc 上的位标志来控制此行为。功能。我在上面显示的选项选择了在模棱两可的情况下较早出现的情况——这通常是首选(恕我直言)。 The project's readme file有关这些选项的更多详细信息以及它们如何控制模糊时间和无效时间行为。

还要考虑，如果您的数据库中有其他一些增量列，例如整数 ID 列，并且您恰好在转换之前和之后的模棱两可期间有行，您可能能够按该辅助列以消除歧义。这通常需要在不明确的时期内对数据进行人工审查 - 或者在您的更新声明中进行一些其他花哨的步骤，但在某些情况下是可能的。