You need a table keeping the time dimension for your MySQL-based data warehouse?
Here’s how you do it:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
# time span 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 ; |
Mind that the mysterious table T only needs to meet one requirement which is that it keeps more rows than you want to generate in time_dimension. The whole idea is that you implicitely select the rows in T and parallel to that you create the actual fields. This is described in more detail in “Generating virtual tables for JOIN operations in MySQL” and also this related stackoverflow question might be interesting: “Odd behaviour of SELECT on MySQL“.
The ISO-standard backed up philosophy behind what a week is and how to count them you’ll find described in “Counting weeks (ISO 8601)“. For a Yankee it might appear offensively European at first glance but at the end of the week it’s as logical as the metric system.
For a working example have a look at this SQL Fiddle.
(original article published on www.joyofdata.de)
Thank you so much! I have experience in SQL, but not to do this kind of insertion.
Hi, thanks for this help. I added the hour column (and altered the
week()command – I’ll take your word for it that the previous numbering is more logical but that doesn’t work as well for my purposes…)I prepopulated
Tusing a trick from stackoverflow. It’s overpopulated actually but too much in this case is better than too little.Thought you might like to see the results. This helped us out: Link to the code.
Pingback: mysql – time dimension | G3n1k's Blog
This is a date dimension, not a time dimension. Time is hh:mm:ss. Useful nonetheless.
Well observed … to introduce time it is sufficient to reduce the incrementing interval to one second and apply functions HOUR(), MINUTE() and SECOND().