Denormalizing Dates

Much of my job has to do with accounting and reporting. Exciting stuff, right? At any rate, it is often useful to denormalize the dates in often-queried tables to both increase performance and ease of querying. That is, we take some date field (I tend to use smalldatetime on SQL server because most of what I do does not need sub-second resolution), in my examples called When_Date and crack it open into its constituent parts. Depending on your needs, this might include the Year, Month, Day of Month, Day of Year, Week of Year, Time, Hour of Day, and whatever else your crazy users can think of.

Instead of worrying about updating a dozen fields each time I update a date, I use a trigger to take the one canonical date field and denormalize it, like so:

CREATE TRIGGER [date_fix] ON dbo.TimeEntry_Log
AFTER INSERT, UPDATE
AS
SET NOCOUNT ON
IF UPDATE(When_Date) BEGIN
    UPDATE TimeEntry_Log SET
        When_Day = DATEADD(DAY,DAY(When_Date)-1,DATEADD(MONTH,MONTH(When_Date)-1,DATEADD(YEAR,YEAR(When_Date)-2000,{d '2000-01-01'}))),
        When_Mon = DATEADD(MONTH,MONTH(When_Date)-1,DATEADD(YEAR,YEAR(When_Date)-2000,{d '2000-01-01'})),
        When_MOY = MONTH(When_Date),
        When_Year = YEAR(When_Date),
        When_Time = DATEADD(YEAR,2000-YEAR(When_Date),DATEADD(MONTH,1-MONTH(When_Date),DATEADD(DAY,1-DAY(When_Date),When_Date))),
        When_Hour = DATEPART(HOUR,When_Date),
        When_WOY = DATEPART(WEEK,When_Date),
        When_Week = DATEDIFF(WEEK,{d '2000-01-01'},When_Date)
    WHERE (id IN (SELECT id FROM inserted))
END
SET NOCOUNT OFF

Of course, you would have an entirely different set of fields, but the concept is the same. In this case, we might take today and break it down thusly:

ColumnTypeValueComments
When_Datesmalldatetime{ts ‘2006-01-24 16:43:32’}The original value
When_Daysmalldatetime{d ‘2006-01-24’}No HH:mm:ss (day granularity)
When_Monsmalldatetime{d ‘2006-01-01’}Month-level granularity
When_MOYtinyint1Month of year
When_Yearsmallint2006Year
When_Timesmalldatetime{ts ‘2000-01-01 16:43:32’}Time component only
When_Hourtinyint16Hour
When_WOYtinyint4Week of year
When_Weeksmallint3Weeks since our epoch

These are just the ones that I use regularly. There are plenty more: quarter, time-of-day rounded to the nearest 15 minutes, fiscal year, etc.

The great thing about a system such as this is that not only does it make for faster, easier queries, but it also tends to make your ColdFusion code easier to read. Instead of building heinous DATEPART statements into your code, you can just choose the correct column. No muss, no fuss.

Published by

Rick Osborne

I am a web geek who has been doing this sort of thing entirely too long. I rant, I muse, I whine. That is, I am not at all atypical for my breed.