将SQL存储的DateTime值迁移到DateTimeOffset最佳做法？ [英] Migrating SQL stored DateTime values to DateTimeOffset best practice?

问题描述

在过去3年中，给定了一个SQL表，其中包含DateTime列和大约100k行的不同日期（本地时间PST值），将这些列值迁移到 DateTimeOffset 以添加缺少的utc tz偏移量信息？

Given a SQL table with a DateTime column and approx 100k rows of various dates (local time PST values) from the past 3 years, what is the best strategy to migrate those column values to DateTimeOffset to "add" the missing utc tz offset info?

现有的DateTime值已经存储，没有任何时区/ utc偏移量的详细信息。所储存的日期总是代表太平洋时间（-800或-700，取决于夏令时）。目标是追溯地将tz偏移量添加到所有现有数据，假设日期来自太平洋时间（无论正确的偏移量是在日期指定的时刻）

The existing DateTime values have been stored without any time zone/utc offset details. The dates stored are always representative of Pacific Time (-800 or -700 depending on Daylight Saving Time). Goal is to retroactively add the tz offset to all existing data with the assumption that the date came from Pacific time (whatever the correct offset was at the moment specified by the date)

• 在SQL中，这种迁移的最佳做法是什么，而不会丢失任何数据或更改现有值？

• Within SQL what is the best practice for this type of migration without losing any data, or changing existing values?

进入下一步，什么是最有效的方法来迁移中等大小的整个数据库（〜100bb在〜100个表中使用2个DateTime每个表的列）使用DateTimeOffset列和值？

Taking it to the next step, what is the most efficient method to migrate an entire database of a moderate size (~100gb in ~100 tables with 2 DateTime columns per table) to use DateTimeOffset columns and values?

PST / PDT切换日期之后的上午2点记录的数据时间会发生什么？是否有数据丢失发生？

What happens to the datetimes that were logged around/during 2am on the PST/PDT changeover date? Is there a data loss that happens?

SQL Server 2008 + C＃4.5

SQL Server 2008 + C# 4.5

如果这不是正确的区域，请指出我正确的方向，谢谢！

If this is not the right area please point me in the right direction, thanks!

编辑：Yay，赏金时间。

Yay, bounty time.

推荐答案

夏令时并不总是精确的科学。例如。在北美使用期间，有一个 2007年的规则更改。因此，建议填写相关日期的表格。例如。对于2000-2013年：

Daylight saving isn't always an exact science. E.g. there was a rule change in 2007 for the period used in North America. For this reason, would suggest populating a table with the relevant dates. E.g. for 2000-2013:

-- Populate a table with PST daylight saving start/end times
-- Example data for 2000-2013 - sourced from
-- http://www.timeanddate.com/worldclock/timezone.html?n=137
CREATE TABLE dst (start DateTime, [end] DateTime);
INSERT INTO dst (start, [end]) VALUES ('02:00 2 Apr 2000', '02:00 29 Oct 2000');
INSERT INTO dst (start, [end]) VALUES ('02:00 1 Apr 2001', '02:00 28 Oct 2001');
INSERT INTO dst (start, [end]) VALUES ('02:00 7 Apr 2002', '02:00 27 Oct 2002');
INSERT INTO dst (start, [end]) VALUES ('02:00 6 Apr 2003', '02:00 26 Oct 2003');
INSERT INTO dst (start, [end]) VALUES ('02:00 4 Apr 2004', '02:00 31 Oct 2004');
INSERT INTO dst (start, [end]) VALUES ('02:00 3 Apr 2005', '02:00 30 Oct 2005');
INSERT INTO dst (start, [end]) VALUES ('02:00 2 Apr 2006', '02:00 29 Oct 2006');
INSERT INTO dst (start, [end]) VALUES ('02:00 11 Apr 2007', '02:00 4 Oct 2007');
INSERT INTO dst (start, [end]) VALUES ('02:00 9 Apr 2008', '02:00 2 Nov 2008');
INSERT INTO dst (start, [end]) VALUES ('02:00 8 Apr 2009', '02:00 1 Nov 2009');
INSERT INTO dst (start, [end]) VALUES ('02:00 14 Apr 2010', '02:00 7 Nov 2010');
INSERT INTO dst (start, [end]) VALUES ('02:00 13 Apr 2011', '02:00 6 Nov 2011');
INSERT INTO dst (start, [end]) VALUES ('02:00 11 Apr 2012', '02:00 4 Nov 2012');
INSERT INTO dst (start, [end]) VALUES ('02:00 10 Apr 2013', '02:00 3 Nov 2013');

当然，您可能需要一个很多作为读者的练习:-)

Of course you may need a lot more than this - will leave doing that as an exercise for the reader :-)

好的，所以让我们说，你填充了上面的表格，并且你的日期/时间是 dt 表 test 。然后，您可以加入上表，执行如下转换：

OK, so let's say you populated the above table with the whole possible range and you have date/times in field dt of table test. Then you can join to the above table and do the conversions like this:

-- Convert sample dates to PST offset with daylight saving where appropriate
SELECT test.dt,
       CAST(CONVERT(VARCHAR(23), test.dt, 126) + -- Convert to ISO8601 format
            CASE WHEN dst.start IS NULL
                 THEN '-08:00' -- No record joined so not within DST period
                 ELSE '-07:00' -- DST record joined so is within DST period
            END AS DateTimeOffset) AS dto
FROM test
LEFT JOIN dst -- Join on daylight savings table to find out whether DST applies
ON test.dt >= dst.start
AND test.dt <= dst.[end]

这是一个 SQL小提琴演示。

这篇关于将SQL存储的DateTime值迁移到DateTimeOffset最佳做法？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！