MYSQL: Generate Calendar Table

gistfile1.txt

DROP TABLE IF EXISTS time_dimension;
CREATE TABLE 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            CHAR(1) DEFAULT 'f' CHECK (holiday_flag in ('t', 'f')),
        weekend_flag            CHAR(1) DEFAULT 'f' CHECK (weekday_flag in ('t', 'f')),
        event                   VARCHAR(50),
        UNIQUE td_ymd_idx (year,month,day),
        UNIQUE td_dbdate_idx (db_date)

) Engine=MyISAM;

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 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'),
                        'f',
                        CASE DAYOFWEEK(currentdate) WHEN 1 THEN 't' WHEN 7 then 't' ELSE 'f' END,
                        NULL);
        SET currentdate = ADDDATE(currentdate,INTERVAL 1 DAY);
    END WHILE;
END
//
DELIMITER ;

TRUNCATE TABLE time_dimension;

CALL fill_date_dimension('1-01-01','2015-01-01');
OPTIMIZE TABLE time_dimension;

Thank you . This is pretty much useful and flawless. Thanks

This is great! Do you have a script for Redshift?

This is great! Minor correction: I believe on Line 13, weekday_flag should be weekend_flag

@ohiosonia: awslabs/amazon-redshift-utils@57123ea

hi there. thanks for your code.
here is a fork which could help ;)
added day of week and boolean flag for weekend ;)

DROP TABLE IF EXISTS dates;
CREATE TABLE dates (
idDate INTEGER PRIMARY KEY, -- year10000+month100+day
fulldate 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
dayOfWeek INTEGER NOT NULL, -- 1 to 7
weekend INTEGER NOT NULL,
UNIQUE td_ymd_idx (year,month,day),
UNIQUE td_dbdate_idx (fulldate)

) 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 dates VALUES (
YEAR(currentdate)*10000+MONTH(currentdate)*100 + DAY(currentdate),
currentdate,
YEAR(currentdate),
MONTH(currentdate),
DAY(currentdate),
QUARTER(currentdate),
WEEKOFYEAR(currentdate),

CASE DAYOFWEEK(currentdate)-1 WHEN 0 THEN 7 ELSE DAYOFWEEK(currentdate)-1 END ,
CASE DAYOFWEEK(currentdate)-1 WHEN 0 THEN 1 WHEN 6 then 1 ELSE 0 END);
SET currentdate = ADDDATE(currentdate,INTERVAL 1 DAY);
END WHILE;
END
//
DELIMITER ;

TRUNCATE TABLE dates;

CALL fill_date_dimension('2014-01-01','2101-01-01');
OPTIMIZE TABLE dates;

For spanish just add as first line:
SET lc_time_names = 'es_ES';

thank you so much! beautiful work!
helped me a lot!

Hey! This table helped me a ton. Super easy. I modified your code to make it populate a table with hours,mins&seconds! Here it is if anyone else wants to use it:

DROP TABLE IF EXISTS time_table;
CREATE TABLE time_table (
id INTEGER PRIMARY KEY, -- year10000+month100+day
db_time TIME NOT NULL,
hour INTEGER NOT NULL,
minute INTEGER NOT NULL, -- 1 to 12
second INTEGER NOT NULL, -- 1 to 31
event VARCHAR(50)
) Engine=MyISAM;

DROP PROCEDURE IF EXISTS fill_date_dimension;
DELIMITER //
CREATE PROCEDURE fill_date_dimension(IN startdate TIME,IN stopdate TIME)
BEGIN
DECLARE currentdate TIME;
SET currentdate = startdate;
WHILE currentdate < stopdate DO
INSERT INTO time_table VALUES (
HOUR(currentdate)*10000+MINUTE(currentdate)*100 + SECOND(currentdate),
currentdate,
HOUR(currentdate),
MINUTE(currentdate),
SECOND(currentdate),
NULL);
SET currentdate = ADDDATE(currentdate,INTERVAL 15 MINUTE);
END WHILE;
END
//
DELIMITER ;

TRUNCATE TABLE time_table;

CALL fill_date_dimension('00:00:00','23:45:00');
OPTIMIZE TABLE time_table;

Awesome.. thnx ..

Great code. Thanks

Thanks!!!!

thanks!!!

Thankssss!! 👍

thank youu very helpfull

Cool

Eight years later and still a huge help, thanks!

It still help. Thanks

Thank you very much for this helpful code and idea 🤩🤩🤩