SWITCHOFFSET() Surprise?

This is the followup post I promised during my recent PASSMN presentation, to explain some of the unorthodox SQL used to convert datetimes between time zones, taking Daylight Savings into consideration.

The Setup

Dave Valentine ( b | t ) was kind enough to be my audience for a dry run of my (at that time) upcoming presentation on Managing Daylight Savings without CLR. He unwittingly found an issue that surprised me, while I was pointing out that you couldn’t use SWITCHOFFSET() on the following dates without knowing the time_zone information (ie, would you pass in –05:00 or –06:00 to convert these to Central Time?):

  • 2012-03-10 22:00 +00:00
  • 2012-03-12 22:00 +00:00

It is a common best practice to store datetimes in UTC and do any conversion after the fact. My presentation lays out a method to create an SSIS package that uses a Script Component to store Daylight Savings information in a lookup table. Creating the lookup table to store Daylight Savings information is relatively easy, and you end up with data that looks like this:

dbo.DaylightSavings

TimeZone AdjustmentStart AdjustmentEnd UTCOffset
Central Standard Time 2012-01-01 00:00 2012-03-11 01:59 -06:00
Central Standard Time 2012-03-11 02:00 2012-11-04 01:59 -05:00

The original code that I showed Dave worked but is wrong for datetimes near the Daylight Savings Time boundary.

WITH DemoDates AS
    (
        SELECT UTCDate = '2012-03-10 22:00 +00:00' UNION
        SELECT UTCDate = '2012-03-12 22:00 +00:00'
    ) ,
    DaylightSavings AS
    (
        SELECT AdjustmentStart = '2012-01-01 00:00', AdjustmentEnd = '2012-03-11 01:59', UTCOffset = '-06:00' UNION
        SELECT AdjustmentStart = '2012-03-11 02:00', AdjustmentEnd = '2012-11-04 01:59', UTCOffset = '-05:00'
    )

    SELECT  UTCDate, CentralTime = SWITCHOFFSET(UTCDate, UTCOffset)
    FROM    DemoDates DD
    JOIN    DaylightSavings DS
            ON  DS.AdjustmentStart  DD.UTCDate ;

After I finished the dry run, Dave’s feedback suggested that I include some times that were nearer to the Daylight Savings Time boundary, which actually began 2012-03-11 02:00 Central Time. That sounded easy enough, but caused me to go off of the rails for awhile. Let’s dig a little deeper.

The Surprise

So, I added a couple of additional datetimes to the presentation and found that the SQL above did not get the expected result. So, I did a little bit of Google-Fu, and found nothing discussing this issue. Plus, the presentation was the next day, and I wanted to work more on the demos. I decided to dig in and try to understand exactly what was happening on the boundaries. The issue was that since datetimes are being stored as UTC, the join actually needs to take that UTC datetime and consider the time that it would be converted to for determination of UTCOffset. Huh?

Let’s try to illustrate, with a table of data (that’s how we all think, right?)

UTCDate Central Standard Time
2012-03-10 22:00 +00:00 2012-03-10 16:00 -06:00
2012-03-11 07:00 +00:00 2012-03-11 01:00 -06:00
2012-03-11 08:00 +00:00 2012-03-11 03:00 -05:00
2012-03-12 22:00 +00:00 2012-03-12 17:00 -05:00

The boundary for the start of Daylight Savings Time for the Central Time Zone is 2012-03-11 02:00, CENTRAL TIME. So, if we want to join to the lookup table, and get the right row, we need to do some gymnastics.

WITH DemoDatesPlusBoundary AS
(
    SELECT UTCDate = '2012-03-10 22:00 +00:00' UNION
    SELECT UTCDate = '2012-03-12 22:00 +00:00' UNION
    SELECT UTCDate = '2012-03-11 07:00 +00:00' UNION
    SELECT UTCDate = '2012-03-11 08:00 +00:00'
) ,
DaylightSavings AS
(
     SELECT AdjustmentStart = '2012-01-01 00:00', AdjustmentEnd = '2012-03-11 01:59', UTCOffset = '-06:00' UNION
     SELECT AdjustmentStart = '2012-03-11 02:00', AdjustmentEnd = '2012-11-04 01:59', UTCOffset = '-05:00'
 )

SELECT  UTCDate, CentralTime = SWITCHOFFSET(UTCDate, UTCOffset)
FROM    DemoDatesPlusBoundary DD
JOIN    DaylightSavings DS
        -- Take the Adjustment and make a DATETIMEOFFSET, then switch to UTC for proper
        -- comparison
        ON DD.UTCDate > SWITCHOFFSET(TODATETIMEOFFSET(AdjustmentStart, UTCOffset), '+00:00')
        AND DD.UTCDate
        -- JOIN below is wrong when the date being converted is on the DST change boundary
        -- so the more complex join above is required
        -- ON DS.AdjustmentStart  DD.UTCDate

Here’s an explanation of what SWITCHOFFSET(TODATETIMEOFFSET(AdjustmentStart, UTCOffset), ‘+00:00’) does (inside to out):

    1. Creates a datetimeoffset value from the AdjustmentStart and UTCOffset from the lookup table
    2. Takes that result and uses SWITCHOFFSET to find out when that AdjustmentStart happens IN UTC TIME

That join is clearly not optimal, but gets the right result, and got it done in time for the presentation. Looking back, I wish I would have done the dry run sooner and I would/will now store the result of SWITCHOFFSET(TODATETIMEOFFSET(AdjustmentStart, UTCOffset), ‘+00:00’) in the lookup table as well, so the calculation only needs to be done when the lookup table is populated, not every time it is joined to.

Add a Comment

Your email address will not be published. Required fields are marked *