Time dimensions are key in certain data mart/warehouse applications. They can prevent your date dimension from growing out of control by reusing the
The specific columns you require are just that, specific to you. But below is a dimension I wrote today for a statistics project which tracks even down to the granularity of a second.
Code is for SQL Server.
Nothing crazy going on here, but in general these are the columns I like to track for ease of reporting. You can argue that an auto-incremented
int value is better as primary key, and you'd likely have a valid case. But an equally compelling argument could be made in the other direction. Just think, will
00:00:01 (representing the first second of the time series) ever represent anything other than what it currently represents? Probably not.
The point here is not to put on the blinders and automatically create an auto-incremented
int surrogate key for all your tables. Think about your data..
CREATE TABLE [dbo].[DimTime] ([TimeKey] TIME NOT NULL , [Hour12] TINYINT NOT NULL , [Hour24] TINYINT NOT NULL , [MinuteOfHour] TINYINT NOT NULL , [SecondOfMinute] TINYINT NOT NULL , [ElapsedMinutes] SMALLINT NOT NULL , [ElapsedSeconds] INT NOT NULL , [AMPM] CHAR(2) NOT NULL , [HHMMSS] CHAR(8) NOT NULL , CONSTRAINT [pk_dimtime] PRIMARY KEY CLUSTERED ([TimeKey]));
To populate, we first define our limits. Next, we use the
cross join'd onto itself acting as our row generator, limiting it to the number of seconds difference between our
@EndTime. What I like about this approach is that we ultimately perform one large insert versus 86400 individual inserts. Next, we extract some useful information to make our final calculations more succinct. And finally, we insert the records.
DECLARE @StartTime TIME = CONVERT(TIME, '00:00:00'); DECLARE @EndTime TIME = CONVERT(TIME, '23:59:59'); WITH [timestamps] AS (SELECT [ts].[TimeKey] , DATEPART(HOUR, [ts].[TimeKey]) + 1 AS [Hour24] , DATEPART(MINUTE, [ts].[TimeKey]) AS [MinuteOfHour] , DATEPART(SECOND, [ts].[TimeKey]) AS [SecondOfMinute] FROM (SELECT DATEADD(SECOND, [x].[rn] - 1, @StartTime) AS [TimeKey] FROM (SELECT TOP (DATEDIFF(SECOND, @StartTime, @EndTime)) ROW_NUMBER() OVER (ORDER BY [s1].[object_id]) AS [rn] FROM [sys].[all_objects] AS [s1] CROSS JOIN [sys].[all_objects] AS [s2] ORDER BY [s1].[object_id]) AS [x] UNION SELECT @EndTime) AS [ts] ) INSERT INTO [dbo].[DimTime] ([TimeKey], [Hour12], [Hour24], [MinuteOfHour], [SecondOfMinute], [ElapsedMinutes], [ElapsedSeconds], [AMPM], [HHMMSS]) SELECT [ts].[TimeKey] , CASE WHEN [ts].[Hour24] > 12 AND [ts].[Hour24] % 12 <> 0 THEN [ts].[Hour24] % 12 WHEN [ts].[Hour24] % 12 = 0 THEN 12 ELSE [ts].[Hour24] END AS [Hour12] , [ts].[Hour24] , [ts].[MinuteOfHour] , [ts].[SecondOfMinute] , ([ts].[Hour24] - 1) * 60 + [ts].[MinuteOfHour] AS [ElapsedMinutes] , (([ts].[Hour24] - 1) * 60 + [ts].[MinuteOfHour]) * 60 + [ts].[SecondOfMinute] AS [ElapsedSeconds] , CASE WHEN [ts].[Hour24] > 12 THEN 'PM' ELSE 'AM' END AS [AMPM] , CONVERT(CHAR(8), [ts].[TimeKey], 108) AS [HHMMSS] FROM [timestamps] AS [ts] ORDER BY [ts].[TimeKey];