nP69Awfm

1;
2DROP TABLE IF EXISTS dates;
3CREATE TABLE dates (
4        id                      INTEGER PRIMARY KEY,  -- year*10000+month*100+day
5        date                 	DATE NOT NULL,
6        year                    INTEGER NOT NULL,
7        month                   INTEGER NOT NULL, -- 1 to 12
8        day                     INTEGER NOT NULL, -- 1 to 31
9        quarter                 INTEGER NOT NULL, -- 1 to 4
10        week                    INTEGER NOT NULL, -- 1 to 52/53
11        day_name                VARCHAR(9) NOT NULL, -- 'Monday', 'Tuesday'...
12        month_name              VARCHAR(9) NOT NULL, -- 'January', 'February'...
13        holiday_flag            CHAR(1) DEFAULT 'f' CHECK (holiday_flag in ('t', 'f')),
14        weekend_flag            CHAR(1) DEFAULT 'f' CHECK (weekday_flag in ('t', 'f')),
15        event                   VARCHAR(50),
16        UNIQUE td_ymd_idx (year,month,day),
17        UNIQUE td_dbdate_idx (date)
18
19) Engine=InnoDB;
20
21DROP PROCEDURE IF EXISTS fill_date_dimension;
22DELIMITER //
23CREATE PROCEDURE fill_date_dimension(IN startdate DATE,IN stopdate DATE)
24BEGIN
25    DECLARE currentdate DATE;
26    SET currentdate = startdate;
27    WHILE currentdate < stopdate DO
28        INSERT INTO dates VALUES (
29                        YEAR(currentdate)*10000+MONTH(currentdate)*100 + DAY(currentdate),
30                        currentdate,
31                        YEAR(currentdate),
32                        MONTH(currentdate),
33                        DAY(currentdate),
34                        QUARTER(currentdate),
35                        WEEKOFYEAR(currentdate),
36                        DATE_FORMAT(currentdate,'%W'),
37                        DATE_FORMAT(currentdate,'%M'),
38                        'f',
39                        CASE DAYOFWEEK(currentdate) WHEN 1 THEN 't' WHEN 7 then 't' ELSE 'f' END,
40                        NULL);
41        SET currentdate = ADDDATE(currentdate,INTERVAL 1 DAY);
42    END WHILE;
43END
44//
45DELIMITER ;
46
47TRUNCATE TABLE dates;
48
49CALL fill_date_dimension('2000-01-01','2100-01-01');
50OPTIMIZE TABLE dates;
51
52SELECT * FROM dates ORDER BY id DESC;