Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Setting Up a Time Dimension Table in MySQL / comment
DROP TABLE IF EXISTS `T`;
CREATE TABLE `T` (
`n` int(11)
);
INSERT INTO `T`(n) SELECT @row := @row + 1 as row FROM
(select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t,
(select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t2,
(select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t3,
(select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t4,
(select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t5,
(select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t6,
(SELECT @row:=0) t7;
time span
SET @d0 =2012-01-01 00:00:00″;
SET @d1 =2030-01-01 00:00:00″;
SET @date = @d0;
set up the time dimension table
DROP TABLE IF EXISTS time_dimension;
CREATE TABLE `time_dimension` (
`date` date DEFAULT NULL,
`id` int NOT NULL,
`y` smallint DEFAULT NULL,
`m` smallint DEFAULT NULL,
`d` smallint DEFAULT NULL,
`yw` smallint DEFAULT NULL,
`w` smallint DEFAULT NULL,
`q` smallint DEFAULT NULL,
`wd` smallint DEFAULT NULL,
`m_name` char(10) DEFAULT NULL,
`wd_name` char(10) DEFAULT NULL,
`h` tinyint default NULL,
PRIMARY KEY (`id`)
);
— populate the table with dates
INSERT INTO time_dimension (`date`,id, y, m, d, yw, w, q, wd, m_name, wd_name, h)
SELECT @date := date_add(@date, interval 1 hour) as date,
integer ID that allows immediate understanding
date_format(@date, “%Y%m%d%HH24″) as id,
year(@date) as y,
month(@date) as m,
day(@date) as d,
date_format(@date, “%x”) as yw,
week(@date) as w,
quarter(@date) as q,
weekday(@date)+1 as wd,
monthname(@date) as m_name,
dayname(@date) as wd_name,
hour(@date) as h
FROM T
WHERE date_add(@date, interval 1 hour) <= @d1
ORDER BY date
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment