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:

Column Type Value Comments
When_Date smalldatetime {ts ‘2006-01-24 16:43:32’} The original value
When_Day smalldatetime {d ‘2006-01-24’} No HH:mm:ss (day granularity)
When_Mon smalldatetime {d ‘2006-01-01’} Month-level granularity
When_MOY tinyint 1 Month of year
When_Year smallint 2006 Year
When_Time smalldatetime {ts ‘2000-01-01 16:43:32’} Time component only
When_Hour tinyint 16 Hour
When_WOY tinyint 4 Week of year
When_Week smallint 3 Weeks 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.