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

@ysnr ysnr commented Jun 10, 2016

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

@ohiosonia

This comment has been minimized.

Copy link

@ohiosonia ohiosonia commented Jun 28, 2016

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

@alleng28

This comment has been minimized.

Copy link

@alleng28 alleng28 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

@yoyoy yoyoy 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

@alejosv alejosv 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

@jccalbuquerque jccalbuquerque commented Feb 1, 2018

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

@SPARTAN142

This comment has been minimized.

Copy link

@SPARTAN142 SPARTAN142 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

@gunjan-mehta gunjan-mehta commented Jul 17, 2018

Awesome.. thnx ..

@gtlg-mstr

This comment has been minimized.

Copy link

@gtlg-mstr gtlg-mstr commented Mar 21, 2019

Great code. Thanks

@iberi22

This comment has been minimized.

Copy link

@iberi22 iberi22 commented Oct 30, 2019

Thanks!!!!

@RussiSunni

This comment has been minimized.

Copy link

@RussiSunni RussiSunni commented Jan 12, 2020

thanks!!!

@hwayoungjun204

This comment has been minimized.

Copy link

@hwayoungjun204 hwayoungjun204 commented May 25, 2020

Thankssss!! 👍

@chloe-tt

This comment has been minimized.

Copy link

@chloe-tt chloe-tt commented May 26, 2020

thank youu very helpfull

@ariefaprilianto

This comment has been minimized.

Copy link

@ariefaprilianto ariefaprilianto commented Jun 6, 2020

Cool

@vikrampant

This comment has been minimized.

Copy link

@vikrampant vikrampant commented Aug 18, 2020

Eight years later and still a huge help, 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.