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.

We are starting to design the building blocks of a data mart/warehouse and we need to be able to support all time zones (our clients are from all over the world). From reading discussions online (and in books), a common solution seems to be to have a separate date and time dimension as well as a timestamp in the fact tables.

However, the question I am having hard time answering is what good does the date and time dimensions actually do for me considering my dynamic time zone requirements? A time dimension makes a little more sense but I'm having hard time with the date dimension. A general design approach for a date dimension usually includes properties such as day name, day of week, month name, etc. The problem I am having with all that is that 11:00 PM on Tuesday, December 31, 2013 in UTC is Wednesday, January 1st, 2014 in all time zones that are after UTC+2.

So if I will have to do all these time zone conversions on each and every query (and report) then what is the point of having and storing these properties that I will probably never use (seems like)? Some people suggest having fact rows for each time zone but that seems ridiculous to me. We need to be able to store millions of records each month.

Others suggest having a time zone bridge table which although makes some sense, it also seems like extra complexity and extra joins to accomplish something that my client apps and reports should easily be able to figure out from a date (reporting will be primarily web-based where there are a myriad of libraries to aide in converting, displaying and formatting dates).

The only thing I can think of is the ease and possibly performance of grouping by date and hour but how bad of a practice is to group by datepart (we're using MS SQL but we will be querying millions of rows) or should we consider just extremely simple date and time dimensions with not much more than hour, day, month and year numbers for the most part as most literals such as Monday wouldn't mean much when time zones come into play?

share|improve this question
1  
I think what you're after is the datetimeoffset datatype and then store all dates in their UTC representation. Then when you need to extract the data, you query the data in it's UTC value and let the client represent it in its local time. –  Allan S. Hansen Oct 18 '13 at 12:50
6  
I can think of no reason I would want to store date independent of time. Store it all as UTC datetime and let the presentation layer worry about localization. –  billinkc Oct 29 '13 at 15:57
1  
I agree with @billinkc. I'm not sure what benefit you would gain from storing date and time separately when you would constantly end up putting them back together to do the time zone conversion. –  mmarie Oct 29 '13 at 16:13
    
@billinkc: "I can think of no reason I would want to store date independent of time." - I can. Whenever you are building a cube off the warehouse. Having separate Date and TimeofDay Dimensions is commonplace and best practice. –  Mitch Wheat Oct 7 at 2:25
    
@MitchWheat Could you help me understand that (perhaps you're composing an answer)? I am an adult company with global sales and at 2300 GMT, I have a strong surge in sales. I drag my slicer into the report and sure, in the US Eastern and Central time zones, I might have some sales going on as people pick up some packaged drinks on the way home but it's 0330 in India, nobody's picking up Kingfisher at that hour and Perth's 6 a.m. Y'all are mighty down under but who's brushing their teeth with VB? Instead, people buy booze after work so 1700ish but I then need to worry about date boundaries –  billinkc Oct 7 at 2:41

2 Answers 2

up vote 3 down vote accepted

Firstly...

Separating Datime/Time into a Date dimension and a Time dimension is definitely the way to go.

To manage multiple time zones you need to duplicate the DateKey and the TimeKey so that you have the following:

  • LocalDateKey
  • LocalTimeKey
  • UtcDateKey
  • UtcTimeKey

You say...

The problem I am having with all that is that 11:00 PM on Tuesday, December 31, 2013 in UTC is Wednesday, January 1st, 2014 in all time zones that are after UTC+2.

By having the 4 columns I've listed above you, will be able to join the fact table to the Date and/or Time dimension Using Table Aliases (in Kimball terminology these aliased dimension tables are known as "Role Playing Dimensions"), so you would have something like the following:

/*
    Assumes the following:
        - [DateLongName] has the format of this example "Tuesday, December 31, 2013"
        - [TimeShortName] has the format of this example "11:00 PM"
        - Both [DateLongName] & [TimeShortName] are strings
*/
select
    -- Returns a string matching this example  "11:00 PM Tuesday, December 31, 2013"
    localTime.TimeShortName + ' ' + localDate.DateLongName
    ,utcTime.TimeShortName + ' ' + utcDate.DateLongName
    ,f.*
from
    FactTableName  AS f

    -- Local Date and Local Time joins          
    inner join dbo.Date  AS localDate
        on localDate.DateKey = f.LocalDateKey

    inner join dbo.Time  AS localTime
        on localTime.TimeKey = f.LocalTimeKey 

    -- Utc Date and Utc Time joins    
    inner join dbo.Date  AS utcDate
        on utcDate.DateKey = f.UtcDateKey

    inner join dbo.Time  AS utcTime
        on utcTime.TimeKey = f.UtcTimeKey 

In closing...

As you're building a data mart, and not an OLTP database, the generation of the Local and Utc times should be performed in your ETL, NOT in any client side applications for the following reasons (apart from localisation of the UTC time to the report reader's perspective):

  • Having the calculation reside in any queries places an extra performance burden on them, multiplied by the number of times you have to run said query for any reports you have (this matters when reading millions of rows)
  • Extra burden of ensuring the calculation is maintained correctly in each query (especially when you take daylight savings time into account)
  • Prevent range scanning of any indexes the column is part of, as you'll be performing a calculation on the column which forces queries to perform index scans instead of seeks (which are typically more expensive as each data page is needed to be read). This is known as being non-sargable (Wikipedia: Sargable) Edit due to comments: This applies if you push the conversion down into the actual query.
  • Using the concept of having the additional UTC dates and times available, there is nothing stopping you from taking this concept and extending it by calling this StandardisedDateKey, or CorporateHQDateKey, where instead of a UTC date table you standardise based on some other business agreed standard
  • Having the two separate column types (Local and UTC), allows for side-by-side comparison across geographic distance. Think -> someone in Australia enters a record that is timestamped with both Local and UTC, someone in New York reads the report with the Local (Australia) date and time and the New York representation of the UTC date and time, thereby seeing that something their Australian counterpart did during the middle of the day (Australia time) happened in the middle of the night their time (New York time). This comparison of time is indispensable in multi-national businesses.
share|improve this answer
2  
Performing presentation layer transformations to go from UTC datetime to a specific timezone isn't going to cause a scan. –  mmarie Oct 29 '13 at 16:12
1  
Your comment on range scanning is inaccurate. This assumes that you would convert the table to Local instead of converting the request range to UTC. @mmarie beat me to the punch on this a bit but my comment still stand as I'm refering to the database level instead of the presentation layer. –  Zane Oct 29 '13 at 16:13
    
Fair comment on doing the conversion at presentation layer, will add an edit. –  futureSPQR Oct 29 '13 at 22:42
    
@Zane - converting the range request to UTC only works for simple queries where the range is identical for the entire query. If your query includes entities in different timezones (e.g. "Yesterday's sales for every store" where each store is in a different timezone and you define "yesterday" in context of the store, not the client) then converting before querying won't work. –  Justin Grant Jul 9 at 17:15
    
Why use separate Date and Time dimensions instead of a single DateTime? A fact table may have several dates, and storing two INTs instead of one for each can add up. –  Jon of All Trades Aug 22 at 22:55

I apologize ahead of time for the brevity of this answer and plan to elaborate when I am not at work.

There are most certainly advantages to having date and time tables as they allow for easy aggregation of your data. In a lot of cases it's the simplest way to sort by month or business days things of that nature. However this doesn't necessarily replace the usefullness of a timestamp. In your particular case a UTC timestamp. Once you have that timestamp all you have to do is change that to local time in the report or presentation layer. In order to avoid range scans make sure you are converting your request range to UTC time as well.

If any other questions or comments feel free to ask.

share|improve this answer

Your Answer

 
discard

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.