Setting Up a Time Dimension Table in MySQL

You need a table keeping the time dimension for your MySQL-based data warehouse?


Here’s how you do it:

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

5 thoughts on “Setting Up a Time Dimension Table in MySQL

  1. Thank you so much!  I have experience in SQL, but not to do this kind of insertion.

  2. 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 T using 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.

  3. Pingback: mysql – time dimension | G3n1k's Blog

  4. 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().

Comments are closed.