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; |
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
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!
Thank you . This is pretty much useful and flawless. Thanks