Date dimension has been so common that probably almost each data warehouse model contains it. But time dimension is not that frequently used. It is generally used in the scenarios whereby you want to get insights out of your data based on some timestamps. In this post, we will see how time dimension can be created using T-SQL.
Before we actually start writing the code for it, let’s quickly pay attention to its core i.e. the grain of this table. Just like date dimension holds its lowest grain at the date level, similarly, time dimension works at the lowest grain of time. In most scenarios, time dimension is created at the grain of seconds so a day is divided into 24 hours, each Hour consists of 60 Minutes and each minute has 60 seconds so our time dimension will have 24x60x60 = 86400 rows in it.
DROP TABLE IF EXISTS dbo.DimTime
GO
CREATE TABLE dbo.DimTime
(
Id INT NOT NULL CONSTRAINT PKC_DimTime PRIMARY KEY CLUSTERED
, Hour24 INT NOT NULL
, Hour24ShortString VARCHAR(2) NOT NULL
, Hour24MinString VARCHAR(5) NOT NULL
, Hour24FullString VARCHAR(8) NOT NULL
, Hour12 INT NOT NULL
, Hour12ShortString VARCHAR(2) NOT NULL
, Hour12MinString VARCHAR(5) NOT NULL
, Hour12FullString VARCHAR(8) NOT NULL
, AmPmCode INT NOT NULL
, AmPmString VARCHAR(2) NOT NULL
, Minute INT NOT NULL
, MinuteCode INT NOT NULL
, MinuteShortString VARCHAR(2) NOT NULL
, MinuteFullString24 VARCHAR(8) NOT NULL
, MinuteFullString12 VARCHAR(8) NOT NULL
, HalfHour INT NOT NULL
, HalfHourCode INT NOT NULL
, HalfHourShortString VARCHAR(2) NOT NULL
, HalfHourFullString24 VARCHAR(8) NOT NULL
, HalfHourFullString12 VARCHAR(8) NOT NULL
, Second INT NOT NULL
, SecondShortString VARCHAR(2) NOT NULL
, FullTimeString24 VARCHAR(8) NOT NULL
, FullTimeString12 VARCHAR(8) NOT NULL
, FullTime TIME(7) NOT NULL
);
GO
Below is the code that is used to populate the table with all its members.
DECLARE @hour INT
, @minute INT
, @second INT
SET @hour = 0
WHILE @hour < 24
BEGIN
SET @minute = 0
WHILE @minute < 60
BEGIN
SET @second = 0
WHILE @second < 60
BEGIN
INSERT INTO dbo.DimTime
(
Id
, Hour24
, Hour24ShortString
, Hour24MinString
, Hour24FullString
, Hour12
, Hour12ShortString
, Hour12MinString
, Hour12FullString
, AmPmCode
, AmPmString
, Minute
, MinuteCode
, MinuteShortString
, MinuteFullString24
, MinuteFullString12
, HalfHour
, HalfHourCode
, HalfHourShortString
, HalfHourFullString24
, HalfHourFullString12
, Second
, SecondShortString
, FullTimeString24
, FullTimeString12
, FullTime
)
SELECT
(@hour*10000) + (@minute*100) + @second AS TimeKey
, @hour AS Hour24
, RIGHT('0'+CONVERT(VARCHAR(2),@hour),2) Hour24ShortString
, RIGHT('0'+CONVERT(VARCHAR(2),@hour),2)+':00' Hour24MinString
, RIGHT('0'+CONVERT(VARCHAR(2),@hour),2)+':00:00' Hour24FullString
, @hour%12 AS Hour12
, RIGHT('0'+CONVERT(VARCHAR(2),@hour%12),2) Hour12ShortString
, RIGHT('0'+CONVERT(VARCHAR(2),@hour%12),2)+':00' Hour12MinString
, RIGHT('0'+CONVERT(VARCHAR(2),@hour%12),2)+':00:00' Hour12FullString
, @hour/12 AS AmPmCode
, CASE WHEN @hour<12 THEN 'AM' ELSE 'PM' END AS AmPmString
, @minute AS MINUTE
, (@hour*100) + (@minute) MinuteCode
, RIGHT('0'+CONVERT(VARCHAR(2),@minute),2) MinuteShortString
, RIGHT('0'+CONVERT(VARCHAR(2),@hour),2)+':'+
RIGHT('0'+CONVERT(VARCHAR(2),@minute),2)+':00' MinuteFullString24
, RIGHT('0'+CONVERT(VARCHAR(2),@hour%12),2)+':'+
RIGHT('0'+CONVERT(VARCHAR(2),@minute),2)+':00' MinuteFullString12
, @minute/30 AS HalfHour
, (@hour*100) + ((@minute/30)*30) HalfHourCode
, RIGHT('0'+CONVERT(VARCHAR(2),((@minute/30)*30)),2) HalfHourShortString
, RIGHT('0'+CONVERT(VARCHAR(2),@hour),2)+':'+
RIGHT('0'+CONVERT(VARCHAR(2),((@minute/30)*30)),2)+':00' HalfHourFullString24
, RIGHT('0'+CONVERT(VARCHAR(2),@hour%12),2)+':'+
RIGHT('0'+CONVERT(VARCHAR(2),((@minute/30)*30)),2)+':00' HalfHourFullString12
, @second AS Second
, RIGHT('0'+CONVERT(VARCHAR(2),@second),2) SecondShortString
, RIGHT('0'+CONVERT(VARCHAR(2),@hour),2)+':'+
RIGHT('0'+CONVERT(VARCHAR(2),@minute),2)+':'+
RIGHT('0'+CONVERT(VARCHAR(2),@second),2) FullTimeString24
, RIGHT('0'+CONVERT(VARCHAR(2),@hour%12),2)+':'+
RIGHT('0'+CONVERT(VARCHAR(2),@minute),2)+':'+
RIGHT('0'+CONVERT(VARCHAR(2),@second),2) FullTimeString12
, CONVERT(TIME,RIGHT('0'+CONVERT(VARCHAR(2),@hour),2)+':'+
RIGHT('0'+CONVERT(VARCHAR(2),@minute),2)+':'+
RIGHT('0'+CONVERT(VARCHAR(2),@second),2)) AS FullTime
SET @second = @second + 1
END
SET @minute = @minute + 1
END
SET @hour = @hour + 1
END
GO
A reference has been taken from https://radacad.com/script-for-creating-and-generating-members-for-time-dimension for the script.
