Steve Aves

When it comes to data analysis, a calendar table can be a real game-changer. It may not seem necessary at first, since most data technologies (SQL, programming languages, visualization softwares, Excel, etc.) have a multitude of functions to help you add, subtract, and transform dates to aggregate by any time period imaginable. But the benefits of a calendar table are many:

• Eliminate calculated fields – Calculated fields aren’t always human readable, which makes it more difficult to determine what you’re up to. Calendar tables can have field names that are easy to recognize. Do you prefer DATE_SUB(currentdate, INTERVAL DAYOFWEEK(currentdate)-1 DAY) or TheFirstOfWeek? I thought so.
• Increase performance – In addition to their inherent calculation time, calculated fields must be joined in temporary tables (slow!). Instead, you can join and aggregate on a lovely, pre-populated and pre-indexed table to make your queries slippery quick.
• Create consistency across technologies – Different technologies handle date formulas differently, with different arguments, and different formats. A calendar table gives you the consistency you need to get the job done faster.
• Establish a full range of dates, even if no data exists for those dates – You can’t query what’s not there. A calendar table will return every date in the range you specify, not just the dates that have the data you’re looking for.
• Allow for fiscal calendar craziness – Fiscal year start in April? Suffering the 4-4-5 blues? Set it and forget it with a calendar table.
• Handle weekends and holidays – Weekends can be manageable without a calendar table, but it’ll make your life easier. And I don’t know of any data technology that handles holidays gracefully (because of their regionality), so set yourself up for success by populating the days that are special to you.

So without further ado, here is some MYSQL code to get us started. Just use the database of your choice, and copy/paste the entirety of this code.

DROP TABLE IF EXISTS date_dimension;
CREATE TABLE date_dimension (
    ID                          INTEGER PRIMARY KEY,
    TheDate                     DATE NOT NULL,
    TheDay                      INTEGER NOT NULL,
    TheDaySuffix                CHAR(2) NOT NULL,
    TheDayName                  VARCHAR(9) NOT NULL,
    TheDayOfWeek                INTEGER NOT NULL,
    TheDayOfWeekInMonth         INTEGER NOT NULL,
    TheDayOfYear                INTEGER NOT NULL,
    IsWeekend                   INTEGER NOT NULL,
    TheWeek                     INTEGER NOT NULL,
    TheISOWeek                  INTEGER NOT NULL,
    TheFirstOfWeek              DATE NOT NULL,
    TheLastOfWeek               DATE NOT NULL,
    TheWeekOfMonth              INTEGER NOT NULL,
    TheMonth                    INTEGER NOT NULL,
    TheMonthName                VARCHAR(9) NOT NULL,
    TheFirstOfMonth             DATE NOT NULL,
    TheLastOfMonth              DATE NOT NULL,
    TheFirstOfNextMonth         DATE NOT NULL,
    TheLastOfNextMonth          DATE NOT NULL,
    TheQuarter                  INTEGER NOT NULL,
    TheFirstOfQuarter           DATE NOT NULL,
    TheLastOfQuarter            DATE NOT NULL,
    TheYear                     INTEGER NOT NULL,
    TheISOYear                  INTEGER NOT NULL,
    TheFirstOfYear              DATE NOT NULL,
    TheLastOfYear	        DATE NOT NULL,
    IsLeapYear                  INTEGER NOT NULL,
    Has53Weeks                  INTEGER NOT NULL,
    Has53ISOWeeks               INTEGER NOT NULL,
    TheFiscalMonth_Apr          INTEGER NOT NULL,
    TheFiscalQuarter_Apr	INTEGER NOT NULL,
    TheFiscalYear_Apr           INTEGER NOT NULL,
    TheFirstOfFiscalYear_Apr    DATE NOT NULL,
    TheLastOfFiscalYear_Apr     DATE NOT NULL,
    TheFirstOfNextFiscal_Apr    DATE NOT NULL,
    TheFiscalMonth_Oct          INTEGER NOT NULL,
    TheFiscalQuarter_Oct        INTEGER NOT NULL,
    TheFiscalYear_Oct           INTEGER NOT NULL,
    TheFirstOfFiscalYear_Oct    DATE NOT NULL,
    TheLastOfFiscalYear_Oct     DATE NOT NULL,
    TheFirstOfNextFiscal_Oct    DATE NOT NULL,
        UNIQUE td_ymd_idx (TheYear,TheMonth,TheDay),
        UNIQUE td_dbdate_idx (TheDate)
) Engine=InnoDB;

DROP PROCEDURE IF EXISTS fill_date_dimension;
DELIMITER //
CREATE PROCEDURE fill_date_dimension(IN startdate DATE,IN stopdate DATE)
BEGIN
    DECLARE currentdate DATE;
    SET currentdate = startdate;
    WHILE currentdate < stopdate DO
        INSERT INTO date_dimension VALUES (
            YEAR(currentdate)*10000+MONTH(currentdate)*100 + DAY(currentdate),
            currentdate,
            DAY(currentdate),
            SUBSTRING(DATE_FORMAT(currentdate,'%D'),-2,2),
            DATE_FORMAT(currentdate,'%W'),
            DAYOFWEEK(currentdate),
            FLOOR(DAY(currentdate)/7)+1,
            DATE_FORMAT(currentdate,'%j'),
            CASE DAYOFWEEK(currentdate) WHEN 1 THEN 0 WHEN 7 THEN 0 ELSE 1 END,
            WEEK(currentdate,6),
            WEEKOFYEAR(currentdate),
            DATE_SUB(currentdate, INTERVAL DAYOFWEEK(currentdate)-1 DAY),
            DATE_ADD(currentdate, INTERVAL 7-DAYOFWEEK(currentdate) DAY),
            WEEK(currentdate)-WEEK(DATE_ADD(MAKEDATE(YEAR(currentdate), 1), INTERVAL (MONTH(currentdate))-1 MONTH))+1,
            MONTH(currentdate),
            DATE_FORMAT(currentdate,'%M'),
            DATE_ADD(MAKEDATE(YEAR(currentdate), 1), INTERVAL (MONTH(currentdate))-1 MONTH),
            DATE_ADD(DATE_ADD(MAKEDATE(YEAR(currentdate), 1), INTERVAL (MONTH(currentdate)) MONTH), INTERVAL -1 DAY),
            DATE_ADD(MAKEDATE(YEAR(currentdate), 1), INTERVAL (MONTH(currentdate)) MONTH),
            DATE_ADD(DATE_ADD(MAKEDATE(YEAR(currentdate), 1), INTERVAL (MONTH(currentdate))+1 MONTH), INTERVAL -1 DAY),
            QUARTER(currentdate),
            DATE_ADD(MAKEDATE(YEAR(currentdate),1), INTERVAL (QUARTER(currentdate)-1)*3 MONTH),
            DATE_ADD(DATE_ADD(MAKEDATE(YEAR(currentdate),1), INTERVAL (QUARTER(currentdate))*3 MONTH), INTERVAL -1 DAY),
            YEAR(currentdate),
            YEAR(currentdate) - CASE WHEN MONTH(currentdate) = 1 AND WEEKOFYEAR(currentdate) > 51 THEN 1 WHEN MONTH(currentdate) = 12 AND WEEKOFYEAR(currentdate) = 1  THEN -1 ELSE 0 END,
            MAKEDATE(YEAR(currentdate), 1),
            DATE_ADD(MAKEDATE(YEAR(currentdate)+1, 1), INTERVAL -1 DAY),
            CASE WHEN (YEAR(currentdate) % 400 = 0) OR (YEAR(currentdate) % 4 = 0 AND YEAR(currentdate) % 100 != 0)  THEN 1 ELSE 0 END,
            WEEK(DATE_ADD(MAKEDATE(YEAR(currentdate)+1, 1), INTERVAL -1 DAY),6),
            WEEKOFYEAR(DATE_ADD(MAKEDATE(YEAR(currentdate)+1, 1), INTERVAL -1 DAY)),
            CASE WHEN MONTH(currentdate) <= 3 THEN MONTH(currentdate) + 9 ELSE MONTH(currentdate) - 3 END,
            CASE WHEN MONTH(currentdate) <= 3 THEN 4 ELSE QUARTER(currentdate)-1 END,
            CASE WHEN MONTH(currentdate) <= 3 THEN YEAR(currentdate) ELSE YEAR(currentdate)+1 END,
            CASE WHEN MONTH(currentdate) <= 3 THEN DATE_ADD(MAKEDATE(YEAR(currentdate)-1, 1), INTERVAL 3 MONTH) ELSE DATE_ADD(MAKEDATE(YEAR(currentdate), 1) , INTERVAL 3 MONTH) END,
            CASE WHEN MONTH(currentdate) <= 3 THEN DATE_ADD(DATE_ADD(MAKEDATE(YEAR(currentdate), 1), INTERVAL 3 MONTH), INTERVAL -1 DAY) ELSE DATE_ADD(DATE_ADD(MAKEDATE(YEAR(currentdate)+1, 1), INTERVAL 3 MONTH), INTERVAL -1 DAY) END,
            CASE WHEN MONTH(currentdate) <= 3 THEN DATE_ADD(MAKEDATE(YEAR(currentdate), 1), INTERVAL 3 MONTH) ELSE DATE_ADD(MAKEDATE(YEAR(currentdate)+1, 1), INTERVAL 3 MONTH) END,
            CASE WHEN MONTH(currentdate) >= 10 THEN MONTH(currentdate) - 9 ELSE MONTH(currentdate) + 3 END,
            CASE WHEN MONTH(currentdate) >= 10 THEN 1 ELSE QUARTER(currentdate)+1 END,
            CASE WHEN MONTH(currentdate) >= 10 THEN YEAR(currentdate)+1 ELSE YEAR(currentdate) END,
            CASE WHEN MONTH(currentdate) >= 10 THEN DATE_ADD(MAKEDATE(YEAR(currentdate), 1), INTERVAL 9 MONTH) ELSE DATE_ADD(MAKEDATE(YEAR(currentdate)-1, 1), INTERVAL 9 MONTH) END,
            CASE WHEN MONTH(currentdate) >= 10 THEN DATE_ADD(DATE_ADD(MAKEDATE(YEAR(currentdate)+1, 1), INTERVAL 9 MONTH), INTERVAL -1 DAY) ELSE DATE_ADD(DATE_ADD(MAKEDATE(YEAR(currentdate), 1), INTERVAL 9 MONTH), INTERVAL -1 DAY) END,
            CASE WHEN MONTH(currentdate) >= 10 THEN DATE_ADD(MAKEDATE(YEAR(currentdate)+1, 1), INTERVAL 9 MONTH) ELSE DATE_ADD(MAKEDATE(YEAR(currentdate), 1), INTERVAL 9 MONTH) END
        );
        SET currentdate = DATE_ADD(currentdate,INTERVAL 1 DAY);
    END WHILE;
END
//
DELIMITER ;

TRUNCATE TABLE date_dimension;

CALL fill_date_dimension('2006-01-01','2041-01-01');
OPTIMIZE TABLE date_dimension;

Here is an example row from the date_dimension table that is created:

                      ID: 20220502
                 TheDate: 2022-05-02
                  TheDay: 2
            TheDaySuffix: nd
              TheDayName: Monday
            TheDayOfWeek: 2
     TheDayOfWeekInMonth: 1
            TheDayOfYear: 122
               IsWeekend: 1
                 TheWeek: 18
              TheISOWeek: 18
          TheFirstOfWeek: 2022-05-01
           TheLastOfWeek: 2022-05-07
          TheWeekOfMonth: 1
                TheMonth: 5
            TheMonthName: May
         TheFirstOfMonth: 2022-05-01
          TheLastOfMonth: 2022-05-31
     TheFirstOfNextMonth: 2022-06-01
      TheLastOfNextMonth: 2022-06-30
              TheQuarter: 2
       TheFirstOfQuarter: 2022-04-01
        TheLastOfQuarter: 2022-06-30
                 TheYear: 2022
              TheISOYear: 2022
          TheFirstOfYear: 2022-01-01
           TheLastOfYear: 2022-12-31
              IsLeapYear: 0
              Has53Weeks: 52
           Has53ISOWeeks: 52
      TheFiscalMonth_Apr: 2
    TheFiscalQuarter_Apr: 1
       TheFiscalYear_Apr: 2023
TheFirstOfFiscalYear_Apr: 2022-04-01
 TheLastOfFiscalYear_Apr: 2023-03-31
TheFirstOfNextFiscal_Apr: 2023-04-01
      TheFiscalMonth_Oct: 8
    TheFiscalQuarter_Oct: 3
       TheFiscalYear_Oct: 2022
TheFirstOfFiscalYear_Oct: 2021-10-01
 TheLastOfFiscalYear_Oct: 2022-09-30
TheFirstOfNextFiscal_Oct: 2022-10-01