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 sqlblog.com 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
go
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
)
go

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
Go

You can see the values by using

SELECT top 5*
FROM   time_of_day

returns:

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