Akom's Tech Ruminations

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)