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

http://jasonhaley.com/blog/archive/2006/06/10/136624.aspx

TrackBack — Sun, 11 Jun 2006 01:06:19 GMT

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

Mladen — Sun, 11 Jun 2006 14:56:07 GMT

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

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

Ward Pond — Tue, 13 Jun 2006 20:59:04 GMT

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

http://sqlservercentral.com/forums/shwmessage.aspx?forumid=145&messageid=286601

TrackBack — Mon, 19 Jun 2006 03:31:48 GMT

http://wagnerblog.com/index.php?p=680

TrackBack — Tue, 14 Nov 2006 03:21:11 GMT

http://sql.ru/subscribe/2006/313.shtml

TrackBack — Tue, 14 Nov 2006 03:22:36 GMT

http://sqlblog.com/blogs/aaron_bertrand/archive/2008/11/28/troubleshooting-service-broker-through-sql-server-error-logs.aspx

TrackBack — Mon, 01 Dec 2008 07:31:30 GMT

DST: It's About Time!

Mon, 09 Mar 2009 22:34:24 GMT

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

As Spring Approaches, a SQL Blogger’s Thoughts Turn to Daylight and Baseball

Ward Pond's SQL Server blog — Tue, 10 Mar 2009 04:33:04 GMT

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

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

Iain Johnson — Wed, 10 Jun 2009 16:25:26 GMT

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

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

Ward Pond — Wed, 10 Jun 2009 20:57:09 GMT

@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

SLA calculations

Iain Johnson — Thu, 11 Jun 2009 22:18:00 GMT

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.

re: SLA calculations

Ward Pond — Fri, 12 Jun 2009 06:24:45 GMT

@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

re: SLA calculations

Iain Johnson — Fri, 12 Jun 2009 14:59:03 GMT

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.

re: SLA Calculations

Ward Pond — Mon, 15 Jun 2009 16:19:09 GMT

@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

http://sqlblog.com/blogs/aaron_bertrand/archive/2008/11/28/troubleshooting-service-broker-through-sql-server-error-logs.aspx

TrackBack — Tue, 20 Oct 2009 11:34:21 GMT