Populating a time dimension table automatically

Question

I am currently working on a reporting project. In my datawarehouse I need a dimension table "Time" containing all dates (since 01-01-2011 maybe?) and which increments automatically everyday having this format yyyy-mm-dd. I'm using MySQL on Debian by the way. thanks JT

possible duplicate of [Dynamically creating date periods using MySQL](http://stackoverflow.com/questions/9552508/dynamically-creating-date-periods-using-mysql) — Mosty Mostacho, Mar 06 '12 at 13:43
Setting Up a Time Dimension Table in MySQL: http://www.joyofdata.de/blog/setting-up-a-time-dimension-table-in-mysql/ — Raffael, Jun 03 '14 at 12:36

Devart · Answer 1 · 2012-03-06T12:42:43.210

1

You can add DATE field and use a query like this -

INSERT INTO table(date_column, column1, column2)
  VALUES(DATE(NOW()), 'value1', 'value2');

Also, you can add TIMESTAMP column with ON UPDATE CURRENT_TIMESTAMP, in this case date-time value will be updated automatically.

Automatic Initialization and Updating for TIMESTAMP

edited Mar 06 '12 at 12:42

answered Mar 06 '12 at 12:37

Devart

119,203
23
166
186

score 1 · Answer 2 · edited May 23 '17 at 10:24

1

See this answer

Or This one

There are a number of suggestions there. If your date range is going to be moderate, perhaps a year or two, and assuming your report uses a stored procedure to return the results, you could just create a temporary table on the fly using a rownum technique with limit to get you all of the dates in the range. Then join with your data as required.

Failing that the Union trick in the second answer seems to perform well according to the comments and can be extended to whatever maximum range you will need. It's very messy though!

edited May 23 '17 at 10:24

Community

1
1

answered Mar 06 '12 at 12:46

Tobsey

3,390
14
24

Yes, actually what I really need does not exceed twelve months or such. I thought about using a simple script of insertion of CURDATE() and scheduling the execution in crontab except that I'm not very familiar with the latter. – Jıhad V. Tawfiq Mar 06 '12 at 12:51
@JıhadV.Tawfiq - You don't need to store the dates. Just create a permanent table with a single int column called `num`with the values 1,2,3....365 (or whatever number you may possibly need). Then `SELECT DATE_ADD(NOW(), INTERVAL -1*num DAY) AS Dates FROM NewNumberTable WHERE num < 365`Use this as a subquery and join it to your remaining data for the report. Every time you run the query it will give you the last 365 dates, regardless of when you run it. – Tobsey Mar 06 '12 at 13:01
@Tobsey Why would you not store the dates? The storage is trivial, it's easier to query and reporting tools can then present real dates to the user. – Pondlife Mar 08 '12 at 11:33
@Pondlife - Yes I'd probably agree. I'd create a table with about as many dates in the past and future as I'd possibly need and then just filter it on the daterange required. No need to add a date each to it each day. – Tobsey Mar 08 '12 at 11:59

score 1 · Answer 3 · edited May 23 '17 at 12:18

This article seems to cover what you want. See also this question for another example of the columns you might want to have on your table. You should definitely generate a large amount of dates in advance instead of updating the table daily; it saves a lot of work and complications. 100 years are only ~36500 rows, which is a small table.

Temporary tables or procedural code are not good solutions for a data warehouse, because you want your reporting tool to be able to access the dimension tables. And if your RDBMS has optimizations for star schema queries (I don't know if MySQL does or not) then it would need to see the dimension too.

score 0 · Answer 4 · answered Sep 26 '18 at 09:18

Here is what I am using to create and populate time dimension table:

DROP TABLE IF EXISTS time_dimension;
CREATE TABLE time_dimension (
        id                      INTEGER PRIMARY KEY,  -- year*10000+month*100+day
        db_date                 DATE NOT NULL,
        year                    INTEGER NOT NULL,
        month                   INTEGER NOT NULL, -- 1 to 12
        day                     INTEGER NOT NULL, -- 1 to 31
        quarter                 INTEGER NOT NULL, -- 1 to 4
        week                    INTEGER NOT NULL, -- 1 to 52/53
        day_name                VARCHAR(9) NOT NULL, -- 'Monday', 'Tuesday'...
        month_name              VARCHAR(9) NOT NULL, -- 'January', 'February'...
        holiday_flag            CHAR(1) DEFAULT 'f' CHECK (holiday_flag in ('t', 'f')),
        weekend_flag            CHAR(1) DEFAULT 'f' CHECK (weekday_flag in ('t', 'f')),
        UNIQUE td_ymd_idx (year,month,day),
        UNIQUE td_dbdate_idx (db_date)

) Engine=MyISAM;

DROP PROCEDURE IF EXISTS fill_date_dimension;
DELIMITER //
CREATE PROCEDURE fill_date_dimension(IN startdate DATE,IN stopdate DATE)
BEGIN
    DECLARE currentdate DATE;
    SET currentdate = startdate;
    WHILE currentdate <= stopdate DO
        INSERT INTO time_dimension VALUES (
            YEAR(currentdate)*10000+MONTH(currentdate)*100 + DAY(currentdate),
            currentdate,
            YEAR(currentdate),
            MONTH(currentdate),
            DAY(currentdate),
            QUARTER(currentdate),
            WEEKOFYEAR(currentdate),
            DATE_FORMAT(currentdate,'%W'),
            DATE_FORMAT(currentdate,'%M'),
            'f',
            CASE DAYOFWEEK(currentdate) WHEN 1 THEN 't' WHEN 7 then 't' ELSE 'f' END
            );
        SET currentdate = ADDDATE(currentdate,INTERVAL 1 DAY);
    END WHILE;
END
//
DELIMITER ;

TRUNCATE TABLE time_dimension;
CALL fill_date_dimension('1800-01-01','2050-01-01');
OPTIMIZE TABLE time_dimension;

Populating a time dimension table automatically

4 Answers4