Akom's Tech Ruminations

Various tech outbursts - code and solutions to practical problems
Code and Hacks

Creating A Basic Date Dimension Table in MySQL

Posted by Admin • Monday, March 23. 2009 • Category: Code and Hacks

After some searching I determined that nobody wants to share ...

So I had to sort it out myself - the result is a pretty basic stored procedure you can call whenever you like.

Procedure and Table


Note that the delimiter is needed to keep mysql from interpreting semicolons in the declaration.
CREATE TABLE IF NOT EXISTS datedim  (
    date_id INT NOT NULL auto_increment,
    fulldate date,
    dayofmonth int,
    dayofyear int,
    dayofweek int,
    dayname varchar(10),
    monthnumber int,
    monthname varchar(10),
    year    int,
    quarter tinyint,
    PRIMARY KEY(date_id)
) ENGINE=InnoDB AUTO_INCREMENT=1000;


delimiter //

DROP PROCEDURE IF EXISTS datedimbuild;
CREATE PROCEDURE datedimbuild (p_start_date DATE, p_end_date DATE)
BEGIN
    DECLARE v_full_date DATE;

    DELETE FROM datedim;

    SET v_full_date = p_start_date;
    WHILE v_full_date < p_end_date DO

        INSERT INTO datedim (
            fulldate ,
            dayofmonth ,
            dayofyear ,
            dayofweek ,
            dayname ,
            monthnumber,
            monthname,
            year,
            quarter
        ) VALUES (
            v_full_date,
            DAYOFMONTH(v_full_date),
            DAYOFYEAR(v_full_date),
            DAYOFWEEK(v_full_date),
            DAYNAME(v_full_date),
            MONTH(v_full_date),
            MONTHNAME(v_full_date),
            YEAR(v_full_date),
            QUARTER(v_full_date)
        );

        SET v_full_date = DATE_ADD(v_full_date, INTERVAL 1 DAY);
    END WHILE;
END;


How to Use


mysql> call datedimbuild('2008-01-01','2012-01-01');
Query OK, 1 row affected (1.69 sec)
mysql> select ** from datedim limit 10;
+---------+------------+------------+-----------+-----------+-----------+-------------+-----------+------+---------+
| date_id | fulldate   | dayofmonth | dayofyear | dayofweek | dayname   | monthnumber | monthname | year | quarter |
+---------+------------+------------+-----------+-----------+-----------+-------------+-----------+------+---------+
|  106108 | 2008-01-01 |          1 |         1 |         3 | Tuesday   |           1 | January   | 2008 |       1 | 
|  106110 | 2008-01-02 |          2 |         2 |         4 | Wednesday |           1 | January   | 2008 |       1 | 
|  106112 | 2008-01-03 |          3 |         3 |         5 | Thursday  |           1 | January   | 2008 |       1 | 
|  106114 | 2008-01-04 |          4 |         4 |         6 | Friday    |           1 | January   | 2008 |       1 | 
|  106116 | 2008-01-05 |          5 |         5 |         7 | Saturday  |           1 | January   | 2008 |       1 | 
|  106118 | 2008-01-06 |          6 |         6 |         1 | Sunday    |           1 | January   | 2008 |       1 | 
|  106120 | 2008-01-07 |          7 |         7 |         2 | Monday    |           1 | January   | 2008 |       1 | 
|  106122 | 2008-01-08 |          8 |         8 |         3 | Tuesday   |           1 | January   | 2008 |       1 | 
|  106124 | 2008-01-09 |          9 |         9 |         4 | Wednesday |           1 | January   | 2008 |       1 | 
|  106126 | 2008-01-10 |         10 |        10 |         5 | Thursday  |           1 | January   | 2008 |       1 | 
+---------+------------+------------+-----------+-----------+-----------+-------------+-----------+------+---------+
10 rows in set (0.00 sec)

Time Dimension


Now if you want a time dimension it's not all that different:
CREATE TABLE IF NOT EXISTS timedim  (
    time_id INT NOT NULL auto_increment,
    fulltime time,
    hour int,
    minute int,
    second int,
    ampm varchar(2),
    PRIMARY KEY(time_id)
) ENGINE=InnoDB AUTO_INCREMENT=1000;


delimiter //

DROP PROCEDURE IF EXISTS timedimbuild;
CREATE PROCEDURE timedimbuild ()
BEGIN
    DECLARE v_full_date DATETIME;

    DELETE FROM timedim;

    SET v_full_date = '2009-03-01 00:00:00';
    WHILE v_full_date < '2009-03-02 00:00:00' DO

        INSERT INTO timedim (
            fulltime ,
            hour ,
            minute ,
            second ,
            ampm
        ) VALUES (
            TIME(v_full_date),
            HOUR(v_full_date),
            MINUTE(v_full_date),
            SECOND(v_full_date),
            DATE_FORMAT(v_full_date,'%p')
        );

        SET v_full_date = DATE_ADD(v_full_date, INTERVAL 1 SECOND);
    END WHILE;
END;

//
delimiter ;

Usage


mysql> call timedimbuild();
Query OK, 1 row affected (1 min 44.80 sec)

mysql> select ** from timedim limit 100,5;
+---------+----------+------+--------+--------+------+
| time_id | fulltime | hour | minute | second | ampm |
+---------+----------+------+--------+--------+------+
|  166738 | 00:01:40 |    0 |      1 |     40 | AM   | 
|  166740 | 00:01:41 |    0 |      1 |     41 | AM   | 
|  166742 | 00:01:42 |    0 |      1 |     42 | AM   | 
|  166744 | 00:01:43 |    0 |      1 |     43 | AM   | 
|  166746 | 00:01:44 |    0 |      1 |     44 | AM   | 
+---------+----------+------+--------+--------+------+
5 rows in set (0.00 sec)

0 Trackbacks

  1. No Trackbacks

11 Comments

Display comments as (Linear | Threaded)
  1. Great artcile. After hours of researching, I got to understand pragmatically how to setup dimension table.Thnks.

  2. man, you saved my day (even if you wrote this post 3 years before)

    i'll be eternally grateful

  3. Thanks! This was very helpful...

  4. thanks lots! couldn't believe this info was nowhere else but here. you really break it down i hope this will save me a lot of time for a reservations script i am trying to code

  5. I am wodering if it's possilbe to set dayofmonth with leading zero until it reaches 10, eg. 01, 02, 03,.. 09, 10, 11, 12,.. 01, 02.. etc.

    Also, I would like to create a table with column called "FileNo" and would like it to be auto_increament, but in front of this number want to present year in two numbers, eg. 1301, 1302, 1303, and when year changes to 2014, I would like all new records automatically changing to like 1404, 1405 etc.

    Thanks!

    Leo

  6. you should insert the values into a temp memory table and then insert from select into the destination table, it lowered my execution time from 50seconds to 50ms

  7. Question... why do we need a date id? can just the date be the primary key and use replace instead of insert? Thanks,

  8. Thank you for the help. I had some performance issues with the timedimbuild() procedure so I updated your example to the following which runs in under 15 seconds (down from 1hr +)

    DROP TABLE IF EXISTS timedim;

    CREATE TABLE timedim ( time_id INT NOT NULL auto_increment, fulltime time, hour int, minute int, second int, ampm varchar(2), PRIMARY KEY(time_id) ) ENGINE=InnoDB AUTO_INCREMENT=1000;

    CREATE TABLE IF NOT EXISTS #Must be non-temporary table b/c temp tables cannot be self joined. ints ( i INT ) AS SELECT (0) i UNION SELECT (1) UNION SELECT (2) UNION SELECT (3) UNION SELECT (4) UNION SELECT (5) UNION SELECT (6) UNION SELECT (7) UNION SELECT (8) UNION SELECT (9);

    DROP PROCEDURE IF EXISTS timedimbuild;

    CREATE PROCEDURE timedimbuild() BEGIN

    DECLARE v_full_date DATETIME;
    
    CREATE TEMPORARY TABLE filler(id INT NOT NULL);
    
    INSERT INTO filler(id)
    SELECT (a.i*10000 + b.i*1000 + c.i*100 + d.i*10 + e.i) id
    FROM ints a JOIN ints b JOIN ints c JOIN ints d JOIN ints e
    WHERE (a.i*10000 + b.i*1000 + c.i*100 + d.i*10 + e.i) 
    
  9. Awesome man!, you saved a lot of my teams time. Thanks

  10. Great article and a big help but I was wondering, is it possible to create a Date/Time Dimension ?

  11. Thank you!

Add Comment


You can use [geshi lang=lang_name [,ln={y|n}]][/geshi] tags to embed source code snippets.
Enclosing asterisks marks text as bold (*word*), underscore are made via _word_.
Standard emoticons like :-) and ;-) are converted to images.
Markdown format allowed


Submitted comments will be subject to moderation before being displayed.