I have a table which has 1 row per event. Each event has a datetime stamp column which has values in UTC time. Each row has an associated local timezone in which the event occurred.

I have a DIM table which contains timezones. This dimension provides the UTC offsets for each timezone, one offset for DST, one offset for STD.

We have a second DIM table which contains daylight savings periods for each year. This table is used to determine if an event occurred in DST period or not (if the event location is a location that observes DST).

I am trying to create a query which outputs the UTC stamps from the first table in their local zone. What "gotchas" should I be watching out for?

There is one "gotcha" that I can't figure out:

The start and end times in the daylight savings dimension are like this:

2010-11-07 02:00    2011-03-13 01:59:59 NO
2011-03-13 02:00    2011-11-06 01:59:59 YES
2011-11-06 02:00    2012-03-11 01:59:59 NO
2012-03-11 02:00    2012-11-04 01:59:59 YES
2012-11-04 02:00    2013-03-10 01:59:59 NO
2013-03-10 02:00    2013-11-03 01:59:59 YES

This works great when looking up a LOCAL time to see if it was in DST period (because the 2:00 AM begin time is applicable to the local timezones), but does not work for looking up a UTC time.

HMM, I think I just answered my own question - I need 2 more columns, BEGIN_DATETIME_UTC, END_DATETIME_UTC. Sometimes it just helps to write out the problem I guess. :) I will still post the question to see if there is anything else I am overlooking.

Are there any other gotchas like the one above that I should be considering? Again, I have UTC times, and need to convert them to a local timezone.

I don't think it matters for this question, but environment is SQL Server 2005, and server is configured to the Pacific Timezone.

  • Wow, I just realized that the DST start and end times can't be converted to UTC just two additional columns - because the values would be different for each timezone. Hmm, I guess I still need thoughts on how to best deal with this? – GWR May 14 '12 at 13:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.