How to migrate DateTime values to DateTimeOffset i

问题:

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?

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)

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

• 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?

• 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

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

Edit: Yay, bounty time.

回答1:

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 :-)

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]

Here's an SQL fiddle demo.

回答2:

SQL Server 2016 introduced the T-SQL syntax AT TIME ZONE to solve these kind of problems.

So if you know the time zone of the data, you can append the offset using a simple alter and update script as shown below (assuming you have a table named MyTable having a datetime2 column named DateTimeColumn):

alter table MyTable 
   alter column DateTimeColumn datetimeoffset;

update Mytable
   set DateTimeColumn = convert(datetime2, DateTimeColumn) AT TIME ZONE 'Pacific Standard Time' 

Daylight Saving Time (DST) is considered and there should be no data loss.

(I know the OP specified SQL Server 2008 and the question was asked and answered a long time ago, but hopefully anyone currently struggling with the same kind of problem will be helped by this answer.)