THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Louis Davidson

Creating and using a Time (not Date) Table/Dimension

Haven’t posted anything useful in a while, so I figured I should.  This was something I was building for a coworker the other day, and I find the best way to flesh out a solution is to think about how it will work for the thousands who might see this post, and the hundreds that might actually read it. My next post figures to be an updating of my calendar table queries here on my blog (since I have pretty much abandoned my spaces blog for now.  Another new feature I am going to start is to include a download of the pertinent source code for posts like this. So the create table and insert statement will be in a link at the end of the post.

One of the posts that I am kind of proud of was my initial post on how to build a date table here. I use that script any time I want to create a date dimension for a warehouse, or for some other application. The last use was a time entry system, where I extended it to include work_week, which instead of being Saturday - Sunday, as a normal week is, is defined as Monday - Sunday (Yeah, that way extra time on the weekend is part of the same week.) But I digress.

Recently, the need came up to do groupings of data on the hour of the day, for call center tracking. When I heard the person who would be doing the coding start to say something about using datepart and such, I said, "I know a better way". If you can calculate the number of minutes past midnight, then I could do the rest with relative ease, much like their current experience with the date dimension.

In the end, the hardest part of using the date dimension is the calculating of seconds past midnight. Not hard so much that it is really difficult, but hard to explain.  Basically you use the old stripping the time off of the datetime trick:

DATEADD(DAY, 0, DATEDIFF(DAY, 0, <referencedColumnName>))

This is a neat trick, datediff(day,0 returns a number of days since the base date for the type, either smalldatetime or datetime, and dateadd(day,0 converts the integer to a date value. Quite fast, and you end up with a datetype without having to cast the value (I have to explain this to everyone I introduce it to, so if you already got it, I am sorry).  Then use datediff to see how many seconds have passed since midnight:

DATEDIFF(mi,DATEADD(DAY, 0, DATEDIFF(DAY, 0, <referencedColumnName>)), <referencedColumnName>)

Simple enough, really, just a bit untidy to type.  In reality, if this is a commonly used value for a table (particularly if you have a datetime value in one of your dimension tables), you can just add a computed column to your dimension (probably essential to use the PERSISTED keyword to ensure it is only calculated once).

alter table <tableName>
                add <columnName> as (datediff(mi,DATEADD(DAY, 0, DATEDIFF(DAY, 0, <referencedColumnName>)), <referencedColumnName>)) persisted

Now, the basics are a table that has a key of the number of seconds past midnight, with the types of attributes you might typically calculate, as usual, it is very likely that you will have some more you might want, or less, depending on your organization. Like if everyone had a fixed lunch time, you could add lunch_time, and include the minutes of the lunch time in it.  Then you could compare, say internet bytes/second during lunch versus work hours.

First create your table.  I have called it time_of_day, using my typical naming convention of having reporting tables named with underscores. I have commented on each column as to its meaning.

set nocount on
create table time_of_day
     time_of_day_key smallint primary key,
     hour_of_day_24 tinyint,                --0-23, military/European time
     hour_of_day_12 tinyint,                --1-12, repeating for AM/PM, for us American types
     am_pm char(2),                         --AM/PM
     minute_of_hour tinyint,                --the minute of the hour, reset at the top of each hour. 0-59
     half_hour tinyint,                     --1 or 2, if it is the first or second half of the hour
     half_hour_of_day tinyint,              --1-24, incremented at the top of each half hour for the entire day
     quarter_hour tinyint,                  --1-4, for each quarter hour
     quarter_hour_of_day tinyint,           --1-48, incremented at the tope of each half hour for the entire day
     string_representation_24 char(5),      --military/European textual representation
     string_representation_12 char(5)       --12 hour clock representation sans AM/PM


Then we load the table from

--digits gives you a set of 10 numbers 0-9
with digits (i) as(
        select 1 as i union all select 2 as i union all select 3 union all
        select 4 union all select 5 union all select 6 union all select 7
        union all select 8 union all select 9 union all select 0)
--sequence produces a set of integers from 0 - 9999
,sequence (i) as (
        SELECT D1.i + (10*D2.i) + (100*D3.i) + (1000*D4.i)
        FROM digits AS D1 CROSS JOIN digits AS D2 CROSS JOIN digits AS D3 CROSS JOIN digits AS D4)
insert into time_of_day(time_of_day_key, hour_of_day_24, hour_of_day_12, am_pm, minute_of_hour, half_hour, half_hour_of_day,
     quarter_hour, quarter_hour_of_day, string_representation_24, string_representation_12)

--calculates the different values for the time table
SELECT i as time_of_day_key
      ,datepart(hh, dateval) as hour_of_day_24
      ,datepart(hh, dateval) % 12 + case when datepart(hh, dateval) % 12 = 0 then 12 else 0 end as hour_of_day_12
      ,case when datepart(hh, dateval) between 0 and 11 then 'AM' else 'PM' end as am_pm
      ,datepart(mi, dateval) AS minute_of_hour
      ,((i/30) % 2) + 1  AS half_hour --note, I made these next 4 values 1 based, not 0. So the first half hour is 1, the second is 2
      ,(i/30) + 1  AS half_hour_of_day  --and for the whole day value, they go from
      ,((i/15) % 4) + 1     AS quarter_hour
      ,(i/15) + 1  AS quarter_hour_of_day
      ,right('0' + cast(datepart(hh, dateval) as varchar(2)),2)+ ':' + right('0' + cast(datepart(mi, dateval) as varchar(2)),2) as string_representation_24
      ,right('0' + cast(datepart(hh, dateval) % 12 + case when datepart(hh, dateval) % 12 = 0 then 12 else 0 end as varchar(2)),2)+ 
':' + right('0' + cast(datepart(mi, dateval) as varchar(2)),2) as string_representation_12
FROM (    SELECT dateadd(minute,i,'20000101') AS dateVal, i
        FROM sequence AS sequence
        WHERE i between 0 and 1439 --number of minutes in a day = 1440 
       ) as dailyMinutes


You can see the values by using

SELECT top 5*
FROM   time_of_day


time_of_day_key hour_of_day_24 hour_of_day_12 am_pm minute_of_hour half_hour half_hour_of_day quarter_hour quarter_hour_of_day string_representation_24 string_representation_12
--------------- -------------- -------------- ----- -------------- --------- ---------------- ------------ ------------------- ------------------------ ------------------------
0               0              12             AM    0              1         1                1            1                   00:00                    12:00
1               0              12             AM    1              1         1                1            1                   00:01                    12:01
2               0              12             AM    2              1         1                1            1                   00:02                    12:02
3               0              12             AM    3              1         1                1            1                   00:03                    12:03
4               0              12             AM    4              1         1                1            1                   00:04                    12:04

To use the values, I will create a test table, in this case to simulate a phone call for a call center:

create table testPhoneCall
    TestPhoneCallId int identity primary key,
    callType  varchar(10),
    callTime    datetime --the point in time of the call

Obviously a real table would have more columns with more descriptive information, but this will do for our purposes.  Using the following insert, I will load the table. Basically I am trying to set about 70 percent of calls to incoming, and then at some random time of day, within 2000 days of 20010101. The go 1000 will load the table with 1000 rows.

insert into testPhoneCall(callType,callTime)
select case when rand()> .7 then 'Incoming' else 'Outgoing' end ,dateadd(minute, rand() * 1439, dateadd(day,(rand() * 2000),('20010101')))
go 1000

Taking a look at the data

select top 10 *
from   testPhoneCall

You should see something along the lines of:

TestPhoneCallId callType   callTime
--------------- ---------- -----------------------
1               Outgoing   2002-10-23 06:46:00.000
2               Outgoing   2004-03-23 14:27:00.000
3               Outgoing   2001-01-13 11:45:00.000
4               Incoming   2002-04-29 03:41:00.000
5               Outgoing   2005-06-30 01:19:00.000
6               Outgoing   2002-06-30 13:15:00.000
7               Outgoing   2005-11-13 06:03:00.000
8               Outgoing   2003-01-01 04:56:00.000
9               Outgoing   2004-09-04 12:26:00.000
10              Outgoing   2005-09-28 08:47:00.000

To use this data with our time_of_day table, we need to get the number of seconds past mighnight the data is.  Using the trick to get the
date without time: DATEADD(DAY, 0, DATEDIFF(DAY, 0, <datevalue>)), I datediff by minutes the timeless date, and the date value with time. You can
do this in a query, or a view, or in my case, I am choosing to add this to the table as a computed column (which is what I am doing with
my data warehouse table that sparked this need:

alter table testPhoneCall
                add callTimeOfDay as (datediff(mi,DATEADD(DAY, 0, DATEDIFF(DAY, 0, callTime)), callTime)) persisted
                                                                            --persisted means it calculates at save time, not run time

Now, we can join to the time_of_day table with our new callTimeOfDay columm, and find out how many calls were in the AM, and which were in the PM

select time_of_day.am_pm, COUNT(*) as numCalls
from   testPhoneCall       
         join time_of_day
                on time_of_day.time_of_day_key = testPhoneCall.callTimeOfDay
group by time_of_day.am_pm

And since we used a random number, it should be about even, and it seems to be:

am_pm numCalls
----- -----------
AM    494
PM    506

And you can mix in data from your table, like the call type, and then see what percentage of calls were in the which half of the hour
and if they were incoming or outgoing calls:

select testPhoneCall.callType, time_of_day.half_hour, COUNT(*) as numCalls
from   testPhoneCall       
         join time_of_day
                on time_of_day.time_of_day_key = testPhoneCall.callTimeOfDay
group by testPhoneCall.callType, time_of_day.half_hour

This returns:

callType   half_hour numCalls
---------- --------- -----------
Incoming   1         157
Outgoing   1         352
Incoming   2         155
Outgoing   2         336

It is a simple as that. Keep in mind that using a table to do time calculations will not always be faster. Using the functions can be faster, though
whenever you are doing groupings/joins a time/date table will often be faster but will always be more convienient.

To download the code for creating and loading the time_of_day table, follow this link: timeTableCreateAndLoad.sql

Published Thursday, February 4, 2010 12:09 AM by drsql

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



Andy said:

Nice script. Works perfectly, generates a nice table. I was able to work this into my warehouse in minutes. Thanks alot!

July 4, 2011 5:26 PM

SkyDkn said:



September 27, 2012 6:23 AM

bizolus said:

Nice work... planning to use this very soon

October 31, 2014 3:12 AM

Pedro said:

Hi there,

Great stuff. I am just wondering about the primary key, though. If the primary key is just an integer number, how easy would it be to create relationships from fact tables with this time table? I mean, wouldn't it be easier if the key was something like 2112 (as in 21 hours and 12 minutes)?

I reckon it would be simpler to create calculated columns to act as the foreign key in fact tables this way.

November 25, 2014 12:02 PM

drsql said:

Sorry Pedro, I totally missed this when I was in the hospital and haven't blogged much since then as I have been recovering physically and (perhaps more importantly) mentally.

Really, whatever works is great, I have just always tended to use the number of time units for the key as it is contiguous. For my date dimension, I do use a smart number key, as you can do something like convert (dateValue ... and end up with the number YYYYMMDD, but in this case, I find it easier to do datediff(time unit... to generate the key.

June 12, 2015 1:01 AM

Geoff Bleakley said:

Thanks, I added an additional military time column without the separator.

June 11, 2016 2:06 PM

Leave a Comment


This Blog


Links to my other sites


Privacy Statement