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 🤩🤩🤩

Thank you helped a lot

Congratulations! Very good!

This was a big help. Thank you

This causes an error:
weekend_flag CHAR(1) DEFAULT 'f' CHECK (weekday_flag in ('t', 'f')),

Error Code: 3813. Column check constraint 'STATS_CALENDAR_chk_2' references other column.

To fix change weekday_flag to weekend_flag in the check definition.

These are both a big help, thank you. How can i combine both the time_dimension and include the 15 minute increments per day from time_table so the base_calender contains each day within the range, and within each day there are 24hrs of 15 minute increments? thanks heaps

This was a big help. Thank you🤠

For people running into slow performance of this query, you could have autocommit on. This commits changes on every while loop iteration.

You can get BLAZINGLY fast with:

set autocommit = 0;
START TRANSACTION;
CALL fill_date_dimension('2001-01-01','2015-01-01');
commit;
set autocommit = 1;

It could be worth including the lines into the procedure definition. Just make sure to store the original autocommit value and assign it back at the end so that your procedure does not have the unwanted side-effect of implicitly changing autocommit. Something like:

...
BEGIN
    DECLARE currentdate DATE;
    SET @original_autocommit = @@autocommit;
    SET autocommit = 0;
    SET currentdate = startdate;
    START TRANSACTION;
    WHILE currentdate < stopdate DO
         ...
    END WHILE;
    COMMIT;
    SET autocommit = @original_autocommit;
END //