Time of Day dimension

There are times when you need a time dimension. Web and operational data is a common use. Over the last month, what were the web statistics by hour of the day. What time of day do we get the most orders? Again, a time of day question.

So, we need a time of day dimension, along with some logic to capture the time of day. I've divided the creation and population of the time of day dimension into two parts. The first part creates and populates a simple five column time dimension. The later part provides several possible enhancements. Every data warehouse is different, so it makes it easier to first build out the core time of day dimension first, and then provides ways to extended it, so that you can extend it the way you need it.

Surrogate Key

While most dimensions are best served with a surrogate key, date and time dimensions are best when the key defines the value. Date and time dimensions never change, with no expectation of change. With absolutely no change, there is no need to use a surrogate key.  Our surrogate key is effectively an integer based compound key derived from the hour, the minute and the second. For example:

timeOfDayKey	fullTimeAltKey	hour	minute	second
0	000000	0	0	0
24639	024639	2	46	39
101324	101324	10	13	24

Data Dictionary

Column	Defintion
timeOfDayKey	Table key, and surrogate key
fullTimeAltKey	Character based time key, will all leading zeros
hour	24 hour clock hour in the day
minute	Minute for the hour
second	Second for the minute in the hour

Table Create

       
  /*  -----------------------------------------------------------
       create the initial table
    
         ----------------------------------------------------------- */
         SET ANSI_NULLS ON
         SET QUOTED_IDENTIFIER ON
         SET ANSI_PADDING ON
         GO
  
         CREATE TABLE [dbo].[dimTimeOfDay](
          timeOfDayKey int not null
         ,fullTimeAltKey char(6)
         ,hour tinyint
         ,minute tinyint
         ,second tinyint
          );
    
      alter table [dimTimeOfDay]
        add constraint PK_dimTimeOfDay PRIMARY KEY  ( timeOfDayKey);
   
      create index idx_dimTimeOfDay ON dbo.dimTimeOfDay (fullTimeAltKey)
        include ( hour, minute, second);


       
 

Initial Data Population

       
/*  -----------------------------------------------------------
        Populate the table
        # Rows:  86400
        ----------------------------------------------------------- */
       
  set nocount on
  declare @dtProcessDate datetime, @dtEndDate datetime, @intCounter int
         ,@intprocessDate int , @strFullTimeAltKey char(6),@intHour tinyint
         ,@intMinute tinyint, @intSecond tinyint
 
  set @dtprocessDate =  dateadd(dd, datediff(dd,0,getdate()),0); 
  set           @intCounter = 0
  select @dtEndDate = dateadd(dd,1,@dtProcessDate)
  select @dtprocessDate as 'process date', @dtEndDate as 'end date'
 
 while @dtProcessDate < @dtEndDate and @intCounter < 100000
   begin
   --print '------------------------------------------------------------'
   -- debug select @intCounter as 'Counter', @dtprocessDate as 'process date', @dtEndDate as 'end date'
 
   -- time parts, w/ explicit conversion from int to tinyint
 select  @intHour     = cast(datepart(hh, @dtProcessDate)  as tinyint)
         ,@intMinute       = cast(datepart(mi, @dtProcessDate)  as tinyint)
         ,@intSecond       = cast(datepart(ss, @dtProcessDate)  as tinyint)
 
  select @strFullTimeAltKey =  
            right( '00' + cast(@intHour   as varchar),2)
          + right( '00' + cast(@intMinute as varchar),2)
          + right( '00' + cast(@intSecond as varchar),2)
 
  select @intprocessDate = cast(@strFullTimeAltKey as int)
 
 -- populate table
 
  insert into dbo.dimTimeOfDay(timeOfDayKey,fullTimeAltKey,hour, minute, second )
  values ( @intprocessDate, @strFullTimeAltKey, @intHour, @intMinute, @intSecond )
   
  -- bump date
  select @dtProcessDate = dateadd(second,1,@dtProcessDate)
  select @intCounter = @intCounter + 1
 
  end

       
 

Possible Enhancements

For presentation purposes, we need something better than these five values. For this post, we have listed out six enhancements. Pick and choose as needed, or add your own.

Presentation Enhancements to the Data Dictionary

Column	Definition
TimeOfDayNameFullMil	00:00:00;  military format, time presentation, including seconds
TimeOfDayNameShtMil	00:00;  military format, time presentation, hour and minute only
TimeOfDayNameFullStd	12:00:00 am/pm;  standard format, time presentation, including seconds
TimeOfDayNameShtStd	12:00 am/pm;  standard format, time presentation, hour and minute only
HourNameStd	12 am/pm; hour only name
MinuteName	00:00 minute with seconds

Table Alter to add Enhancements

       

  alter table dimTimeOfDay
  add TimeOfDayNameFullMil char(8)
  
  alter table dimTimeOfDay
  add TimeOfDayNameShtMil char(5)
  
  alter table dimTimeOfDay
  add TimeOfDayNameFullStd char(11)
  
  alter table dimTimeOfDay
  add TimeOfDayNameShtStd char(8)
  
  alter table dimTimeOfDay
  add HourNameStd char(5)
  
  alter table dimTimeOfDay
  add MinuteName char(5)


       
 

Populate Enhanced columns

       

update dbo.dimTimeOfDay
set

 TimeOfDayNameFullMil = right('00' + cast(hour as varchar),2) + ':' + right('00'    + cast(minute as varchar),2) + ':' +right( '00' + cast(second as varchar),2)

,TimeOfDayNameShtMil = right('00' + cast(hour as varchar),2) + ':'
  + right('00' + cast(minute as varchar),2)                      

,TimeOfDayNameFullStd = case 
  when hour = 0 then '12' + ':' + right('00' + cast(minute as varchar),2) + ':' +right( '00' + cast(second as varchar),2)  + ' am' 
  when hour >= 1 and hour <=11 then cast(hour as varchar) + ':' + right('00' + cast(minute as varchar),2) + ':' +right( '00' + cast(second as     varchar),2)  +' am'
  when hour = 12 then '12' + ':' + right('00' + cast(minute as varchar),2) + ':' +right( '00' + cast(second as varchar),2)  + ' pm'
  when hour >= 13 then cast((hour-12)as varchar) + ':'          + right('00' + cast(minute as varchar),2) + ':' +right( '00' + cast(second as varchar),2)  + ' pm'
     else '?' end

,TimeOfDayNameShtStd = case 
 when hour = 0 then '12' + ':'  + right('00' + cast(minute as varchar),2)  + ' am'
 when hour >= 1 and hour <=11 then cast(hour as varchar) +  ':'  + right('00' + cast(minute as varchar),2) + ' am'
 when hour = 12 then '12' +  ':'          + right('00' + cast(minute as varchar),2)   + ' pm'
 when hour >= 13 then cast((hour-12)as varchar) + ':'  + right('00' + cast(minute as varchar),2) +  ' pm'
    else '?' end

,HourNameStd = case 
 when hour = 0 then '12 am'
 when hour >= 1 and hour <=11 then cast(hour as varchar) + ' am'
 when hour = 12 then '12 pm'
 when hour >= 13 then cast((hour-12)as varchar) + ' pm'
    else '?' end

,MinuteName = right('00' + cast(minute as varchar),2) + ':' +right( '00' + cast(second as varchar),2);