Skip to content

Instantly share code, notes, and snippets.

@bryhal
Created November 22, 2012 02:08
Show Gist options
  • Save bryhal/4129042 to your computer and use it in GitHub Desktop.
Save bryhal/4129042 to your computer and use it in GitHub Desktop.
MYSQL: Generate Calendar Table
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;
@trevor-grantme
Copy link

This was a big help. Thank you

@matthysk
Copy link

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.

@whaley66
Copy link

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

@liren-sun
Copy link

This was a big help. Thank you🤠

@peyruz
Copy link

peyruz commented Mar 30, 2023

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment