Time Dimension with Day Light Savings
2 posters
Page 1 of 1
Time Dimension with Day Light Savings
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
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
Re: Time Dimension with Day Light Savings
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.
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.
Re: Time Dimension with Day Light Savings
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 ?
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
Re: Time Dimension with Day Light Savings
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).
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).
Similar topics
» Combined Date Time dimension vs. Separate Day and Time dimensions and timezones
» Time dimension design for cross time zone and custom calendar warehouse, 1min granularity
» Time Dimension, NULLs and Time datatype
» time dimension
» Time Dimension Vs FACT
» Time dimension design for cross time zone and custom calendar warehouse, 1min granularity
» Time Dimension, NULLs and Time datatype
» time dimension
» Time Dimension Vs FACT
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
|
|