Ward Pond's SQL Server blog

Ruminating on issues pertinent to the design and development of sound databases and processes under Microsoft SQL Server 2008, SQL Server 2005, and SQL Server 2000 (while reserving the right to vent about anything else that's on my mind)

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

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

  • Comments 16
  • Likes

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.

 

I’d be very interested to hear from anyone who’s found a more efficient and reliable way to address this issue.
Comments
  • Great stuff!

    I posted a similat post but with less rigouruos demand for accuracy, which i guess it's based on what your app does :)

    and i think that Daylight savings should be canceled all over the world.
    it would simplify things, no? :))

  • Thanks, Jason and Mladen..

    Mladen, why not just have everyone in the world set their clock to the same value?  Then the whole problem goes away.  :)  

    Hmmm..  But then the old saying, "it's five o'clock somewhere" would have to be revisited..  there are always costs..  that's one of the things that keeps us employed.. :)

  • Daylight Saving Time, DST, has finally returned. We sprung forward yesterday. I love DST! For years here

  • As we progress towards the Ides of March, one of my best friends-who-I’ve-never-met, Jimmy May , notes

  • Great post - many thanks! It's an almost identical problem to the one I have for a global helpdesk with regional centres. However, I need to calculate two SLA times for a call - the time by when the caller should have been contacted about their issue (typically within an hour) and the time by which the problem should be solved for them (typically within 2 working days).

    The calculation needs to take account of working hours (so a call logged 30 minutes before the helpdesk closes for the day, should be 'contacted' within 30 minutes of the helpdesk opening on it's next working day. I also need to take account of public holidays, i.e. non-working days, other than weekends. I'm surprised you weren't asked to provide that ability too, otherwise the phone will ring on a public holiday but no one is there to answer ? Maybe the phones get switched to a recorded message on such days :)

  • @Iain Johnson: Thanks for your kind and thoughtful comments.  A few responses for you:

    We do indeed have a Holiday table in our solution, which I omitted from the original post for the sake of brevity and simplicity.  It's populated by timezone and country (two things we know about our service centers) and is JOINed into the UDF.  It renders time in the same manner as the PhoneQueueSchedule table above.

    It seems to me that, with this modification, you could write three UDFs (or one that takes SLALengthInMinutes as a parameter - better for maintainability) and answer the question, "When will I be past a particular SLA for a particular contact?".

    Hope this helps!  Please write back if there's anything more I can provide.

        -wp

  • Ward, thanks for your response. I should’ve realised you’d simplified matters for readers.

    I can see where I can add in the use of a Holidays table to your UDF, although our holiday table contains just dates, e.g. 2009-12-25 00:00:00. Adding the following WHERE clause seems to work:

    WHERE dbo.udfTruncateTime(DATEADD(mi, COALESCE(tzdo.UTCOffsetInMinutesWithinInterval, tz.UTCOffset), @UTCTimeToTest)) NOT IN (SELECT HolidayDate FROM dbo.Holiday)

    i.e. where the time being tested (after accounting for the timezone offset) does not fall on a holiday.

    However, I wonder if the above is too simple and wouldn’t be reliable, as you indicated you would JOIN to the Holiday table and from the description of your Holiday table, it sounds like you specify the UTC time for the holiday duration, e.g. for France which is 1 hour ahead of UTC in the winter, Christmas Day would start at 2009-12-25 01:00:00 and have a duration of 1440 minutes.  Please could you provide the structure of your holiday table and the JOIN condition used?

    As for the SLA calculation I have to do, do you think it can be done in a set-based way ? That certainly would be awesome. Our current code is procedural rather than set-based, and uses recursion to look for the next working day and split up the SLALengthInMinutes (if spanning working days) to work out the SLA datetime. Whilst I can see how your fnIsPhoneQueueOpen UDF works to determine if a center is open or not at a specified UTC time, not being a SQL whizz, I can’t see how I can write a UDF to return the date "…when will I be past a particular SLA…”. It's asking a lot but if you could provide any more pointers, that would be terrific. I’m intrigued as to the 3 UDF’s you suggested. What would they do ?

    Thanks in advance, Iain.

  • @Iain: Hello again!

    My Holiday table looks the same as PhoneQueueSchedule, except that it has its own unique primary key, a "Holiday" datetime column instead of the day of the week, and the TimeZoneId column is removed.  Also, the column names which reference phone queues reference holidays instead.

    We render each holiday as 24 hours (StartMinutesPastLocalMidnight = 0; ClosedLengthInMinutes = 1440 (60 min * 24 hr)).  We need to define a record for each phone queue/holiday combination (just as we do for hours of operation), as holidays for us are local observances rather than timezone specific.

    Once all of this set-up work is done, we simply add a call to it to end the query in the UDF as follows:

    LEFT OUTER JOIN    dbo.Holiday h

       --      by PhoneQueue

       ON      h.PhoneQueueId    = pq.PhoneQueueId

       --      by date (converted to local time; datetimes converted to nvarchar to truncate hh:mm:ss)

       AND     CONVERT(nvarchar(40),h.Holiday,110)     = CONVERT(nvarchar(40),DATEADD(mi,COALESCE(tzdo.UTCOffsetInMinutesWithinInterval,tz.UTCOffset),@UTCTimeToTest),110)

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

               h.StartMinutesPastLocalMidnight

               AND

               (h.StartMinutesPastLocalMidnight + h.ClosedDurationInMinutes)

       WHERE   h.PhoneQueueId IS NULL

    If the outer join succeeds, h.PhoneQueueId is not null, and the query doesn't return data (it shouldn't, because it's a holiday for that phone queue).  If the outer join fails, h.PhoneQueueId is null (it's not a holiday), and the query returns data if it otherwise should.  Voila!

    You can do similar math with the datetime-related intrinsic functions and this data to determine "what time/date will it be when 120 minutes of work time have elapsed?"

    I know this intuitively, but I've not yet been required to do the work.  Your inquiry changes that.

    It's getting pretty late here so I'm not going to attempt anything tonight, but I'll write a new post on that topic just as soon as I have a little time to get creative outside of working hours. *g*  Figure by the end of the weekend for sure.

    Thanks for your detailed and stimulating questions.  Please let me know if there's anything else you need beyond what we're already discussing.

    Thanks again and all the best!

        -wp

  • Hi Ward, thank you so much for your response. It’s very kind of you to offer to write the SLA calc for me.

    I’ve created the Holiday table you describe, as follows:

    CREATE TABLE [dbo].[Holiday](

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

    [PhoneQueueId] [int] NOT NULL,

    [Holiday] [datetime] NOT NULL,

    [StartMinutesPastLocalMidnight] [int] NOT NULL,

    [ClosedDurationInMinutes] [int] NOT NULL

    )

    and added a few Holiday records which all have their StartMinutesPastLocalMidnight = 0 and ClosedDurationInMinutes = 1440. I’ve added the LEFT JOIN you give to the end of the SELECT statement and it all works (I had to add a PhoneQueue table too). However, I don’t see why you use:

    BETWEEN h.StartMinutesPastLocalMidnight

    AND h.StartMinutesPastLocalMidnight + h.ClosedDurationInMinutes)

    Instead of just:

    BETWEEN 0 AND 1400

    I suppose your way is more flexible, ie. for half-day holidays perhaps or early closing?

    Incidentally, this UDF is now perfect for another piece of information we calculate – whether a call was logged with the helpdesk “out of hours”, i.e. when it is closed.

    I look forward to your next post. Do you already have a number one fan? :)

    Cheers, Iain.

  • @Iain: I hope you had a good weekend.

    This "little" task has been a bit more effort than I thought it would be, but the good news is that the only real task I have left is to integrate Holiday processing into my sample.

    I don't know exactly when that's going to get done.  I've got a very busy week ahead of me..  but this project is definitely under my skin..

    I don't want to make any rash promises, but I certainly hope I won't be more than a couple of days.

    Thanks for your patience..  and the brainteaser!

        -wp

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment