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:
BEGIN_DATETIME END_DATETIME IS_DST
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.
