Setting Up a Time Dimension Table in MySQL / comment

gistfile1.sql

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