MYSQL: Generate Calendar Table - With Holidays

gistfile1.sql

/* GENERATE CALENDAR TABLE FOR MYSQL WITH WEEKEND AND HOLIDAYS
 * Added easter-function from http://planet.mysql.com/entry/?id=30328
 * Added Norwegian holidays, holidays are marked with a '1'
 * Create function first, to be used in procedure
 * Updated (2019-03-19): Changed Easter calculation, fixed some errors
 */
DELIMITER //
CREATE FUNCTION EasterSunday(inYear YEAR) RETURNS DATE DETERMINISTIC
BEGIN
    DECLARE a, b, c, d, e, f, g, h, i, k, l, m, n, p INT;

    DECLARE es DATE;

    SET a = MOD(inYear, 19);
    SET b = FLOOR(inYear / 100);
    SET c = MOD(inYear, 100);
    SET d = FLOOR(b / 4);
    SET e = MOD(b, 4);
    SET f = FLOOR((b + 8) / 25);
    SET g = FLOOR((b - f + 1) / 3);
    SET h = MOD((19 * a + b - d - g + 15), 30);
    SET i = FLOOR(c / 4);
    SET k = MOD(c, 4);
    SET l = MOD((32 + 2 * e + 2 * i - h - k), 7);
    SET m = FLOOR((a + 11 * h + 22 * l) / 451);
    SET n = FLOOR((h + l - 7 * m + 114) / 31);
    SET p = MOD((h + l - 7 * m + 114), 31) + 1;

    SET es = CONCAT_WS('-', inYear, n, p);

    RETURN es;
END
//
DELIMITER ;

DROP TABLE IF EXISTS tbl_time_dimension;
CREATE TABLE tbl_time_dimension (
        id                      INTEGER PRIMARY KEY,  -- year*10000+month*100+day
        db_date                 DATE NOT NULL,
        year                    INTEGER NOT NULL,
        month                   INTEGER NOT NULL, -- 1 to 12
        day                     INTEGER NOT NULL, -- 1 to 31
        quarter                 INTEGER NOT NULL, -- 1 to 4
        week                    INTEGER NOT NULL, -- 1 to 52/53
        day_name                VARCHAR(9) NOT NULL, -- 'Monday', 'Tuesday'...
        month_name              VARCHAR(9) NOT NULL, -- 'January', 'February'...
        holiday_flag            TINYINT(1) DEFAULT '0',
        weekend_flag            TINYINT(1) DEFAULT '0',
        event                   VARCHAR(50),
        UNIQUE td_ymd_idx (year,month,day),
        UNIQUE td_dbdate_idx (db_date),
        INDEX (weekend_flag),
        INDEX (holiday_flag),
        INDEX (year),
        INDEX (month),
        INDEX (day)

) Engine=MyISAM;

DROP PROCEDURE IF EXISTS fill_time_dimension;
DELIMITER //
CREATE PROCEDURE fill_time_dimension(IN startdate DATE,IN stopdate DATE)
BEGIN
    DECLARE currentdate DATE;
    SET currentdate = startdate;
    WHILE currentdate < stopdate DO
        INSERT INTO tbl_time_dimension VALUES (
                        YEAR(currentdate)*10000+MONTH(currentdate)*100 + DAY(currentdate),
                        currentdate,
                        YEAR(currentdate),
                        MONTH(currentdate),
                        DAY(currentdate),
                        QUARTER(currentdate),
                        WEEKOFYEAR(currentdate),
                        DATE_FORMAT(currentdate,'%W'),
                        DATE_FORMAT(currentdate,'%M'),
                        CASE
            WHEN DAY(currentdate)=1 AND MONTH(currentdate)=1 THEN '1' -- New Year's Day
            WHEN DAY(currentdate)=1 AND MONTH(currentdate)=5 THEN '1' -- May Day
            WHEN DAY(currentdate)=17 AND MONTH(currentdate)=5 THEN '1' -- Norways National Day
            WHEN DAY(currentdate)=25 AND MONTH(currentdate)=12 THEN '1' -- Christmas Day
            WHEN DAY(currentdate)=26 AND MONTH(currentdate)=12 THEN '1' -- Boxing Day
            WHEN DAY(EasterSunday(YEAR(currentdate)))=CAST(DAY(currentdate) AS UNSIGNED) AND MONTH(EasterSunday(YEAR(currentdate)))=CAST(MONTH(currentdate) AS UNSIGNED) THEN '1' -- Easter (Sunday)
            WHEN DAY(EasterSunday(YEAR(currentdate)))=CAST(DAY(SUBDATE(currentdate,INTERVAL 1 DAY)) AS UNSIGNED) AND MONTH(EasterSunday(YEAR(currentdate)))=CAST(MONTH(SUBDATE(currentdate,INTERVAL 1 DAY)) AS UNSIGNED) THEN '1' -- Easter (Monday)
            WHEN DAY(EasterSunday(YEAR(currentdate)))=CAST(DAY(ADDDATE(currentdate,INTERVAL 2 DAY)) AS UNSIGNED) AND MONTH(EasterSunday(YEAR(currentdate)))=CAST(MONTH(ADDDATE(currentdate,INTERVAL 2 DAY)) AS UNSIGNED) THEN '1' -- Easter (Friday)
            WHEN DAY(EasterSunday(YEAR(currentdate)))=CAST(DAY(ADDDATE(currentdate,INTERVAL 3 DAY)) AS UNSIGNED) AND MONTH(EasterSunday(YEAR(currentdate)))=CAST(MONTH(ADDDATE(currentdate,INTERVAL 3 DAY)) AS UNSIGNED) THEN '1' -- Easter (Thursday)
            WHEN DAY(EasterSunday(YEAR(currentdate)))=CAST(DAY(SUBDATE(currentdate,INTERVAL 39 DAY)) AS UNSIGNED) AND MONTH(EasterSunday(YEAR(currentdate)))=CAST(MONTH(SUBDATE(currentdate,INTERVAL 39 DAY)) AS UNSIGNED) THEN '1' -- Ascension
            WHEN DAY(EasterSunday(YEAR(currentdate)))=CAST(DAY(SUBDATE(currentdate,INTERVAL 49 DAY)) AS UNSIGNED) AND MONTH(EasterSunday(YEAR(currentdate)))=CAST(MONTH(SUBDATE(currentdate,INTERVAL 49 DAY)) AS UNSIGNED) THEN '1' -- Whit Sunday
            WHEN DAY(EasterSunday(YEAR(currentdate)))=CAST(DAY(SUBDATE(currentdate,INTERVAL 50 DAY)) AS UNSIGNED) AND MONTH(EasterSunday(YEAR(currentdate)))=CAST(MONTH(SUBDATE(currentdate,INTERVAL 50 DAY)) AS UNSIGNED) THEN '1' -- Whit Monday
            ELSE '0' END,
                        CASE DAYOFWEEK(currentdate) WHEN 1 THEN '1' WHEN 7 THEN '1' ELSE '0' END,
                        NULL);
        SET currentdate = ADDDATE(currentdate,INTERVAL 1 DAY);
    END WHILE;
END
//
DELIMITER ;

TRUNCATE TABLE tbl_time_dimension;

CALL fill_time_dimension('2015-01-01','2030-01-01');
OPTIMIZE TABLE tbl_time_dimension;