Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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;
@ysnr

This comment has been minimized.

Copy link

commented Jun 10, 2016

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

@ohiosonia

This comment has been minimized.

Copy link

commented Jun 28, 2016

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

@alleng28

This comment has been minimized.

Copy link

commented Nov 28, 2016

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

@zach-data

This comment has been minimized.

@yoyoy

This comment has been minimized.

Copy link

commented Jan 21, 2017

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;

@alejosv

This comment has been minimized.

Copy link

commented May 26, 2017

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

@jccalbuquerque

This comment has been minimized.

Copy link

commented Feb 1, 2018

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

@SPARTAN142

This comment has been minimized.

Copy link

commented Jun 17, 2018

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;

@gunjan-mehta

This comment has been minimized.

Copy link

commented Jul 17, 2018

Awesome.. thnx ..

@gtlg-mstr

This comment has been minimized.

Copy link

commented Mar 21, 2019

Great code. Thanks

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.