MYSQL: Generate Calendar Table

gistfile1.txt

DROP TABLE IF EXISTS calendar;
CREATE TABLE calendar (
        id                      INTEGER PRIMARY KEY,  -- year*10000+month*100+day
        date                    DATE NOT NULL,
        year                    INTEGER NOT NULL,
        month                   INTEGER UNSIGNED NOT NULL, -- 1 to 12
        day                     INTEGER UNSIGNED NOT NULL, -- 1 to 31
        quarter                 INTEGER UNSIGNED NOT NULL, -- 1 to 4
        week                    INTEGER UNSIGNED NOT NULL, -- 1 to 52/53
        dayofweek               INTEGER UNSIGNED NOT NULL, -- 1 to 7
        is_weekend              TINYINT(1) DEFAULT 0,
        is_holiday              TINYINT(1) DEFAULT 0,
        UNIQUE td_ymd_idx (year,month,day),
        UNIQUE td_dbdate_idx (date)

);

DROP PROCEDURE IF EXISTS fill_calendar;

CREATE PROCEDURE fill_calendar(IN startdate DATE,IN stopdate DATE)
BEGIN
    DECLARE currentdate DATE;
    SET currentdate = startdate;
    WHILE currentdate < stopdate DO
        INSERT INTO calendar VALUES (
                        YEAR(currentdate)*10000+MONTH(currentdate)*100 + DAY(currentdate),
                        currentdate,
                        YEAR(currentdate),
                        MONTH(currentdate),
                        DAY(currentdate),
                        QUARTER(currentdate),
                        WEEKOFYEAR(currentdate),
                        1 + ( ( dayofweek(currentdate) + 5 ) % 7 ), -- ODBC to ISO conversion
                        CASE DAYOFWEEK(currentdate) WHEN 1 THEN 1 WHEN 7 then 1 ELSE 0 END,
                        0
                );
        SET currentdate = ADDDATE(currentdate,INTERVAL 1 DAY);
    END WHILE;
END
//
DELIMITER ;

TRUNCATE TABLE calendar;

CALL fill_calendar('2000-01-01','2050-01-01');
OPTIMIZE TABLE calendar;