Ward Pond's SQL Server blog

Database Programming: Slaying The Time Zone Conversion Beast (or at least rendering it comatose)

Does anybody know what time it is?

Does anybody really care about time?

          -Robert Lamm/Chicago

One of the most vexing issues in developing and deploying global systems is handling time zone conversions among the various locales one is servicing around the world.  While this might appear to be a trivial issue on its surface, those of us who’ve delved into it in any depth liken the experience to peeling away the layers of an onion.

Last year, I was called upon to build a feature for my team's product to determine whether or not a particular support phone queue was “open for business” at a given time.  In our schema, support queues are tied to the location of the primary support group for that queue, and the queue’s hours of operation are rendered in the local time for that location.  However, calls might be sent to that queue from agents all over the world.

So, what we needed was a solution that would reliably tell a user of our system whether a queue that is open from, say, 9:00am through 5:00pm Monday through Friday in Redmond, WA is open “now,” whether the question is asked in Redmond, Dallas, Hyderabad, or anywhere else our support agents might be working.

Let me say at the outset that I have not yet encountered the “perfect” solution to this issue; there are simply too many variables to deal with this issue unambiguously and in its totality.  For instance, there are several towns in the US state of Indiana where Daylight Savings Time is observed on one side of the street but not the other.  An unambiguous solution would involve rendering a table of every street address in the world, including its time zone rules.

I’ve seen several solutions which involve querying the registry settings on a client machine to determine its time zone, and then apply the appropriate Daylight Savings increment to calculate the offset to UTC (Universal Time Coordinate, sometimes erroneously called GMT or Greenwich Mean Time (GMT is the local time in Greenwich, England.  GMT observes the British analog to Daylight Savings; UTC is GMT with no seasonal adjustment applied)).

The problem I see with this approach is that we’re relying on the client machine to have its time zone parameters set correctly.  While this is frequently true, it takes only one erroneously configured machine to break this logic.  In our application, an error means that we send a customer’s call to a closed queue; the phone would ring and ring, and nobody would answer it.  To say the least, this is not the sort of customer experience we want our system to provide.

So, the best we can do is to render the information we’ve got in our database and process it correctly.  The solution I built involved three tables and a user defined function.  The PhoneQueueSchedule table renders the hours of operation for each phone queue, for each day of the week:

CREATE TABLE [dbo].[PhoneQueueSchedule] (

    [PhoneQueueScheduleId]                    [int]           NOT NULL IDENTITY (1, 1) NOT FOR REPLICATION,

    [PhoneQueueId]                            [int]           NOT NULL,

    [DayOfWeekId]                             [int]           NOT NULL,

    [TimeZoneId]                              [int]           NOT NULL,

    [QueueStartMinutesPastLocalMidnight]      [int]           NOT NULL,       

    [QueueOpenDurationInMinutes]              [int]           NOT NULL

)

The final four columns in this table require a bit of explanation.  DayOfWeekId is (hopefully) exactly what it sounds like; Sunday is DayOfWeekId 1 and Saturday is DayOfWeekId 7.  TimeZoneId is a pointer to the local TimeZone where the queue's hours of operation are defined (the table rendering this data is described below).  The final two columns render the hours of operation in a manner which simplifies the calculations we're going to make in the UDF.  If the queue is open from 9:00am to 5:00pm local time, the QueueStartMinutesPastLocalMidnight will be 540 (9 hours * 60 minutes = midnight + 9 hours = 9am), while QueueOpenDurationInMinutes will be 480 (8 hours * 60 minutes), the elapsed time between the time the queue opens and the time it closes.

The TimeZone table renders basic time zone information, including the base offset from UTC (the “non-Daylight-Savings” value):

CREATE TABLE [dbo].[TimeZone] (

    [TimeZoneId]    [int]           NOT NULL IDENTITY (1, 1) NOT FOR REPLICATION,

    [TimeZoneName]  [nvarchar](255) NOT NULL,

    [UTCOffset]     [int]           NOT NULL

)

I discovered in my research that while the United States has a fairly cut-and-dried rule for when Daylight Savings starts (first Sunday in April) and ends (last Sunday in October), there are many countries that apply these changes in a far less rigid (some might even say haphazard) form.  In order to do this job reliably, it’s therefore necessary to render the starting and ending UTC date for Daylight Savings for each year, for each Time Zone where it applies.  The TimeZoneDaylightOffset table fills this role:

CREATE TABLE [dbo].[TimeZoneDaylightOffset] (

    [TimeZoneDaylightOffsetId]          [int]       NOT NULL IDENTITY (1, 1) NOT FOR REPLICATION,

    [TimeZoneId]                        [int]       NOT NULL,

    [DaylightIntervalStartUTC]          [datetime]  NOT NULL,

    [DaylightIntervalEndUTC]            [datetime]  NOT NULL,

    [UTCOffsetInMinutesWithinInterval]  [int]       NOT NULL

)

Believe it or not, when these tables are fully populated we can perform our test in a single SELECT statement.  The fnIsPhoneQueueOpen UDF performs this calculation:

SET DATEFIRST 7

GO

CREATE FUNCTION dbo.fnIsPhoneQueueOpen

(

    @PhoneQueueId       int,

    @UTCTimeToTest      datetime

)

RETURNS bit

AS

BEGIN

    DECLARE @Answer bit

    -- assume "failure"..

    SET     @Answer = 0

    -- let the database set "success"..

    SELECT  @Answer = 1

    FROM    dbo.TimeZone tz

    --  check if daylight savings active

    LEFT JOIN

            dbo.TimeZoneDaylightOffset tzdo

    ON      tzdo.TimeZoneId     = tz.TimeZoneId

    AND     @UTCTimeToTest

            BETWEEN

            tzdo.DaylightIntervalStartUTC

            AND

            tzdo.DaylightIntervalEndUTC

    --  now get the schedule..

    JOIN    dbo.PhoneQueueSchedule pqs

    --      by timezone..

    ON      pqs.TimeZoneId      = tz.TimeZoneId

    --      by day of the week (translated to "queue's local day of the week")

    AND     pqs.DayOfWeekId     = DATEPART(dw,DATEADD(mi,COALESCE(tzdo.UTCOffsetInMinutesWithinInterval,tz.UTCOffset),@UTCTimeToTest))

    --      by UTC time (translated to "minutes after midnight in queue's local time")

    AND     -- hours (translated to minutes)

            (DATEPART(hh,DATEADD(mi,COALESCE(tzdo.UTCOffsetInMinutesWithinInterval,tz.UTCOffset),@UTCTimeToTest))*60)

            +

            -- minutes

            DATEPART(mi,DATEADD(mi,COALESCE(tzdo.UTCOffsetInMinutesWithinInterval,tz.UTCOffset),@UTCTimeToTest))

            BETWEEN

            pqs.QueueStartMinutesPastLocalMidnight

            AND

            (pqs.QueueStartMinutesPastLocalMidnight + pqs.QueueOpenDurationInMinutes)

    --  finally get the phone queue..

    JOIN    dbo.PhoneQueue pq

    ON      pqs.PhoneQueueId    = pq.PhoneQueueId

    AND     pq.PhoneQueueId     = @PhoneQueueId

    RETURN  @Answer

END

This code will reliably perform the conversion and answer the question for every time zone for which data is stored in the schema.  If we address the “across the street in Indiana” case by rendering two distinct time zone records for Indiana, we can even handle that case – as long as we know which side of the street our phone queue is configured for.  Note also that at no point in this calculation were we concerned with the time zone setting on our user's machine.  This code produces an accurate result wherever our user is sitting and however his or her machine is configured.