Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


Handling time in a date/time field

Handling time in a date/time field

Handling time in a date/time field

I am trying to figure out how to take some tables and turn them into star schemas.  My simplest example is a table that is keyed by a date/time field (containing both a date and a time) and then several quantity fields.  These quantities are recorded every few minutes.

Kimball says on page 245 of "The Data Warehouse Lifecycle Toolkit" to split the date/time field up into two separate fields, one for date and one for time.  I did this and was able to join my resulting fact table to my date dimension (day level).

Now my question is what do I do with the time field?  My users are going to want to report these quantities by hour or maybe half hour.  Do I also need a time dimension down the second?  Kimball says to leave it in the fact table as an integer, but that won't help my users.  Any advice?

RE: Handling time in a date/time field

I always seperate my dates and times into seperate dimensions.  I have written a script that creates a time dimension that is

--Half Hour
---QTR Hour
----5 Minute

I have the time broke out a number of ways such as actual time in both 12 and 24 hour formats 3:00 PM 15:00.

This helps with usability.  If you create time based levels below the day in a date dimension then you have 24 Hours, 1440 minutes or 86400 members at the leaf depending howdeep you decide to go.  It also makes it harder to report elements like Sales between 3pm and 9pm across all days. by having these elements seperate you reduce the number of dimension elements and make it easier for users to navigate.  Also my experience is that users think of dates and times as completely different elements.

Shoot Me! Shoot Me NOW!!!
                           - Daffy Duck

RE: Handling time in a date/time field

Well put Paul. My solution as well.

RE: Handling time in a date/time field

Thanks for the feedback.  That is the direction I'll head...

RE: Handling time in a date/time field

Paul, just to clarify, what goes into the half hour field for example?  Do you put "10:30" in there for every record between "10:01" and "10:30"?  Or do you put a 1 for first half hour and a 2 for second half hour?

This is the structure I was thinking of:

Seconds from midnight (int) -- KEY
12 hour time (string)
24 hour time (string)
12 hour (int)
24 hour (int)
minute (int)
second (int)
am/pm (string)

So example record would be:
"1:30:10 PM"

So how does your half hour, quarter hour and five minute fields work?


RE: Handling time in a date/time field

as I said I have a number of other attributes to display, to the user that breaks out the time in any way I can forsee them asking for.

As for how the half hour is displayed:

10:00 - 10:59
- 10:00 - 10:29
  10:30 - 10:59
-- 10:30 - 10:44
   10:45 - 10:59
--- 10:45 - 10:49
    10:50 - 10:54
    10:55 - 10:59

My data is served up using SSAS so I can enable attribute hierarchies allowing the user to interact with

Hour 1
Hour 2
Hour 24

This type of break out was helpful in my last project where the business day wasn't midnight to 11:59 but typically 3am to 2:59 am.

Shoot Me! Shoot Me NOW!!!
                           - Daffy Duck

RE: Handling time in a date/time field

Interesting...  Sorry I am so slow, but in the database what would the half hour field contain for 10:25?  Would it contain an integer 1 for the first half hour?  Or would it contain a string "10:00"?  Or would it contain 36,000 for the number of seconds since midnight?


RE: Handling time in a date/time field

The HalfHourDecription field would contain 10:00 - 10:29 since this table is populated by a standard script and populates the same I make the HalfHourID field the integer value for what number half hour it is in this case it is 22.

I haven't yet encountered a project where I have had to go down to the second.  Maybe high volume sales places like QVC or something like that where an accurate sales trend is crucial but I can't find the value in going that deep.

Shoot Me! Shoot Me NOW!!!
                           - Daffy Duck

RE: Handling time in a date/time field

Thanks Paul, that makes sense...

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close