TF3CYzXv

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