Creating a Time Dimension Using T-SQL Script

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.