April 12, 2011 at 11:50 am
I have historical data for almost 15 years and have to load it into a fact table. Corresponding dimension tables have already been created with separate date and time dimensions. The fact table has been set up such that it has the date, time and other dimensional keys from the corresponding dimension tables as foreign keys along with measures. I have to load the facts (which has hourly granularity) such that the time foreign key points to the UTC adjusted hour rather than the local time stamp hour e.g. facts/measures for 5am should point to 11am (I am in CDT so the offset from UTC is 6hours). I also have to take care of DST changes while calculating the offset.
Could someone please outline the process that I should be using for performing this task?
Thanks,
Sam
April 12, 2011 at 1:47 pm
Create a table showing the offset to UTC time for different time ranges, and then query that table to get the correct UTC offset to convert the local time to UTC.
StartDTEndDTUTCOffSet
1998-10-25 02:00.00.0001999-04-04 01:59.59.997-6
1999-04-04 02:00.00.0001999-10-31 01:59.59.997-5
1999-10-31 02:00.00.0002000-04-02 01:59.59.997-6
You can find historical DST time changes here:
http://www.timeanddate.com/worldclock/clockchange.html?n=405
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply