MYSQL: Generate Calendar Table

gistfile1.txt

DROP TABLE IF EXISTS time_dimension;
CREATE TABLE time_dimension (
        id                      INTEGER PRIMARY KEY AUTO_INCREMENT,
        db_date                 DATETIME NOT NULL,
        year                    INTEGER NOT NULL,
        month                   INTEGER NOT NULL, -- 1 to 12
        day                     INTEGER NOT NULL, -- 1 to 31
        hour                    INTEGER NOT NULL, -- 0 to 23
        UNIQUE td_ymd_idx (year,month,day,hour),
        UNIQUE td_dbdate_idx (db_date)

) Engine=MyISAM;

DROP PROCEDURE IF EXISTS fill_date_dimension;
DELIMITER //
CREATE PROCEDURE fill_date_dimension(IN startdate DATETIME,IN stopdate DATETIME)
BEGIN
    DECLARE currentdate DATETIME;
    SET currentdate = startdate;
    WHILE currentdate < stopdate DO
        INSERT INTO time_dimension (db_date, year, month, day, hour) VALUES (
                        currentdate,
                        YEAR(currentdate),
                        MONTH(currentdate),
                        DAY(currentdate),
                        HOUR(currentdate));
        SET currentdate = ADDDATE(currentdate,INTERVAL 1 HOUR);
    END WHILE;
END
//
DELIMITER ;

TRUNCATE TABLE time_dimension;
 
CALL fill_date_dimension(from_unixtime(1502348400000/1000),from_unixtime(1502355600000/1000));
OPTIMIZE TABLE time_dimension;