Im trying to make a mysql table with the following fields: Year,month,day,hour,minute
From 10 years ago to 10 years ahead.
I have this that creates it for days, but it doesnt include hours and minutes
SET @d0 = "2012-01-01";
SET @d1 = "2012-12-31";
SET @date = date_sub(@d0, interval 1 day);
# 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,
PRIMARY KEY (`id`)
);
# populate the table with dates
INSERT INTO time_dimension
SELECT @date := date_add(@date, interval 1 day) as date,
# integer ID that allows immediate understanding
date_format(@date, "%Y%m%d") as id,
year(@date) as y,
month(@date) as m,
day(@date) as d,
date_format(@date, "%x") as yw,
week(@date, 3) as w,
quarter(@date) as q,
weekday(@date)+1 as wd,
monthname(@date) as m_name,
dayname(@date) as wd_name
FROM T
WHERE date_add(@date, interval 1 day) <= @d1
ORDER BY date
;
how can i modify it so it also do hours and minutes? Thanks!
If you opt for a Time Dimension, it is recommended that you separate Date and Time dimensions, combining both will result in >10m records for your dimension while separating Date and Time dimensions will give you 7300 row for the Date Dimension and 1440 row for the Time Dimension (hour/minute) you will then need a foregin key to the Time Dimension in your fact table the same way you are designing it with the Date Dimension.