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. 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.

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

Leave a Reply

Your email address will not be published. Required fields are marked *