Time Dimension with Day Light Savings

View previous topic View next topic Go down

Time Dimension with Day Light Savings

Post  logic4fun on Thu May 13, 2010 1:59 pm

I know time dimension has been adressed here in many threads but what i didnt find is dimension which takes care of the DST changes.

I have a time dimension table that has 86400 rows which represent each second of the day. But my question is how do i handle the DST day which has 25 hours or more seconds than 86400 ?

Thanks for your time and looking at this

Prasad

logic4fun

Posts : 2
Join date : 2010-05-13

View user profile

Back to top Go down

Re: Time Dimension with Day Light Savings

Post  ngalemmo on Thu May 13, 2010 2:38 pm

It begs the question... what do YOU want to do about it?

If you are recording time as local time, then, it is what it is. A day in the fall when the clocks go back do not have 25 hours, it has one hour twice (2:00:00 am - 2:59:59 am) and in the spring there is nothing at that time. It's an anomoly that happens twice a year and most of the time, it doesn't matter.

In situations where it does matter, don't use local time. Use UTC time to record the event against the dimension and convert to local time when doing reports. Store local time as a timestamp (i.e. degenerate dimension) on the fact.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Time Dimension with Day Light Savings

Post  logic4fun on Thu May 13, 2010 2:56 pm

Thanks for the response.
We do record the time in UTC and each individual record gets rolled up to an HOUR level to perform Hour level summaries. This worked fine so far until we decided to create a time dimension to get nice attributes like Work Hours, Off Hours etc.

So now what i have is I will have to roll up by UTC but cant create a dimension table that I can use that as my TIMEKEY. Because if i use UTC as the Key for my Time dimension then my timedimension would be really huge table.

So is it a good idea to keep the UTC in tact and create an HOUR key (1 - 24) and join it to time dimension or any other route ?

logic4fun

Posts : 2
Join date : 2010-05-13

View user profile

Back to top Go down

Re: Time Dimension with Day Light Savings

Post  ngalemmo on Thu May 13, 2010 3:55 pm

Yes, you derive whatever portion of the timestamp you need to get the key to reference the dimension. If you use time of day from UTC to build a reference to your existing time of day dimension, I do not see how it would make the dimension any larger.

Now, if you store both local and UTC dimensional references, for both date and time of day, you would actually be able to report a 25 hour day (local time) by grouping on local date, UTC date, UTC time (hour of day) and local time (hour of day).
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Time Dimension with Day Light Savings

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum