MCgPQQrr

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        day_name_fr             VARCHAR(9) NOT NULL, -- 'Monday', 'Tuesday'...
12        month_name              VARCHAR(9) NOT NULL, -- 'January', 'February'...        
13        month_name_fr           VARCHAR(9) NOT NULL, -- 'January', 'February'...
14        holiday_flag            CHAR(1) DEFAULT 'f' CHECK (holiday_flag in ('t', 'f')),
15        weekend_flag            CHAR(1) DEFAULT 'f' CHECK (weekday_flag in ('t', 'f')),
16        event                   VARCHAR(50),
17        UNIQUE td_ymd_idx (year,month,day),
18        UNIQUE td_dbdate_idx (db_date)
19
20) Engine=MyISAM;
21
22DROP PROCEDURE IF EXISTS fill_date_dimension;
23DELIMITER //
24CREATE PROCEDURE fill_date_dimension(IN startdate DATE,IN stopdate DATE)
25BEGIN
26    DECLARE currentdate DATE;
27    SET currentdate = startdate;
28    WHILE currentdate < stopdate DO
29        INSERT INTO time_dimension VALUES (
30                        YEAR(currentdate)*10000+MONTH(currentdate)*100 + DAY(currentdate),
31                        currentdate,
32                        YEAR(currentdate),
33                        MONTH(currentdate),                        
34                        DAY(currentdate),
35                        QUARTER(currentdate),
36                        WEEKOFYEAR(currentdate),
37                        DATE_FORMAT(currentdate,'%W'),
38                        case when DATE_FORMAT(currentdate,'%W') = 'Monday' then 'Lundi'
39                        when DATE_FORMAT(currentdate,'%W') = 'Tuesday' then 'Mardi'
40                        when DATE_FORMAT(currentdate,'%W') = 'Wednesday' then 'Mercredi'
41                        when DATE_FORMAT(currentdate,'%W') = 'Thursday' then 'Jeudi'
42                        when DATE_FORMAT(currentdate,'%W') = 'Friday' then 'Vendredi'
43                        when DATE_FORMAT(currentdate,'%W') = 'Saturday' then 'Samedi'
44                        ELSE 'Dimanche' END,
45                        DATE_FORMAT(currentdate,'%M'),
46                        case when DATE_FORMAT(currentdate,'%M') = 'January' then 'Janvier'
47                        when DATE_FORMAT(currentdate,'%M') = 'February' then 'Février'
48                        when DATE_FORMAT(currentdate,'%M') = 'March' then 'Mars'
49                        when DATE_FORMAT(currentdate,'%M') = 'April' then 'Avril'
50                        when DATE_FORMAT(currentdate,'%M') = 'May' then 'Mai'
51                        when DATE_FORMAT(currentdate,'%M') = 'June' then 'Juin'
52                        when DATE_FORMAT(currentdate,'%M') = 'July' then 'Juillet'
53                        when DATE_FORMAT(currentdate,'%M') = 'August' then 'Août'
54                        when DATE_FORMAT(currentdate,'%M') = 'September' then 'Septembre'
55                        when DATE_FORMAT(currentdate,'%M') = 'October' then 'Octobre'
56                        when DATE_FORMAT(currentdate,'%M') = 'November' then 'Novembre'
57                        ELSE 'Décembre' END,
58                        'f',
59                        CASE DAYOFWEEK(currentdate) WHEN 1 THEN 't' WHEN 7 then 't' ELSE 'f' END,
60                        NULL);
61        SET currentdate = ADDDATE(currentdate,INTERVAL 1 DAY);
62    END WHILE;
63END
64//
65DELIMITER ;
66
67TRUNCATE TABLE time_dimension;
68
69CALL fill_date_dimension('2015-01-01','2030-01-01');
70OPTIMIZE TABLE time_dimension;