Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
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? –  robarwebservices May 14 '12 at 13:26

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.