Skip to content

Instantly share code, notes, and snippets.

@dctucker

dctucker/gist:f1914a3c225743422f17

Forked from bryhal/gist:4129042
Last active July 14, 2018 12:59
Download ZIP

Revisions

  1. dctucker revised this gist Aug 21, 2015. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion gistfile1.txt
    View file
    Original file line number Diff line number Diff line change
    @@ -42,5 +42,5 @@ DELIMITER ;

    TRUNCATE TABLE calendar;

    CALL fill_calendar('1-01-01','2015-01-01');
    CALL fill_calendar('2000-01-01','2050-01-01');
    OPTIMIZE TABLE calendar;
  2. dctucker revised this gist Aug 21, 2015. 1 changed file with 23 additions and 26 deletions.
    49 changes: 23 additions & 26 deletions gistfile1.txt
    View file
    Original file line number Diff line number Diff line change
    @@ -1,49 +1,46 @@
    DROP TABLE IF EXISTS time_dimension;
    CREATE TABLE time_dimension (
    DROP TABLE IF EXISTS calendar;
    CREATE TABLE calendar (
    id INTEGER PRIMARY KEY, -- year*10000+month*100+day
    db_date DATE NOT NULL,
    date DATE NOT NULL,
    year INTEGER NOT NULL,
    month INTEGER NOT NULL, -- 1 to 12
    day INTEGER NOT NULL, -- 1 to 31
    quarter INTEGER NOT NULL, -- 1 to 4
    week INTEGER NOT NULL, -- 1 to 52/53
    day_name VARCHAR(9) NOT NULL, -- 'Monday', 'Tuesday'...
    month_name VARCHAR(9) NOT NULL, -- 'January', 'February'...
    holiday_flag CHAR(1) DEFAULT 'f' CHECK (holiday_flag in ('t', 'f')),
    weekend_flag CHAR(1) DEFAULT 'f' CHECK (weekday_flag in ('t', 'f')),
    event VARCHAR(50),
    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 (db_date)
    UNIQUE td_dbdate_idx (date)

    ) Engine=MyISAM;
    );

    DROP PROCEDURE IF EXISTS fill_date_dimension;
    DELIMITER //
    CREATE PROCEDURE fill_date_dimension(IN startdate DATE,IN stopdate 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 time_dimension VALUES (
    INSERT INTO calendar VALUES (
    YEAR(currentdate)*10000+MONTH(currentdate)*100 + DAY(currentdate),
    currentdate,
    YEAR(currentdate),
    MONTH(currentdate),
    DAY(currentdate),
    QUARTER(currentdate),
    WEEKOFYEAR(currentdate),
    DATE_FORMAT(currentdate,'%W'),
    DATE_FORMAT(currentdate,'%M'),
    'f',
    CASE DAYOFWEEK(currentdate) WHEN 1 THEN 't' WHEN 7 then 't' ELSE 'f' END,
    NULL);
    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 time_dimension;
    TRUNCATE TABLE calendar;

    CALL fill_date_dimension('1-01-01','2015-01-01');
    OPTIMIZE TABLE time_dimension;
    CALL fill_calendar('1-01-01','2015-01-01');
    OPTIMIZE TABLE calendar;
  3. @bryhal bryhal created this gist Nov 22, 2012.
    49 changes: 49 additions & 0 deletions gistfile1.txt
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,49 @@
    DROP TABLE IF EXISTS time_dimension;
    CREATE TABLE time_dimension (
    id INTEGER PRIMARY KEY, -- year*10000+month*100+day
    db_date DATE NOT NULL,
    year INTEGER NOT NULL,
    month INTEGER NOT NULL, -- 1 to 12
    day INTEGER NOT NULL, -- 1 to 31
    quarter INTEGER NOT NULL, -- 1 to 4
    week INTEGER NOT NULL, -- 1 to 52/53
    day_name VARCHAR(9) NOT NULL, -- 'Monday', 'Tuesday'...
    month_name VARCHAR(9) NOT NULL, -- 'January', 'February'...
    holiday_flag CHAR(1) DEFAULT 'f' CHECK (holiday_flag in ('t', 'f')),
    weekend_flag CHAR(1) DEFAULT 'f' CHECK (weekday_flag in ('t', 'f')),
    event VARCHAR(50),
    UNIQUE td_ymd_idx (year,month,day),
    UNIQUE td_dbdate_idx (db_date)

    ) Engine=MyISAM;

    DROP PROCEDURE IF EXISTS fill_date_dimension;
    DELIMITER //
    CREATE PROCEDURE fill_date_dimension(IN startdate DATE,IN stopdate DATE)
    BEGIN
    DECLARE currentdate DATE;
    SET currentdate = startdate;
    WHILE currentdate < stopdate DO
    INSERT INTO time_dimension VALUES (
    YEAR(currentdate)*10000+MONTH(currentdate)*100 + DAY(currentdate),
    currentdate,
    YEAR(currentdate),
    MONTH(currentdate),
    DAY(currentdate),
    QUARTER(currentdate),
    WEEKOFYEAR(currentdate),
    DATE_FORMAT(currentdate,'%W'),
    DATE_FORMAT(currentdate,'%M'),
    'f',
    CASE DAYOFWEEK(currentdate) WHEN 1 THEN 't' WHEN 7 then 't' ELSE 'f' END,
    NULL);
    SET currentdate = ADDDATE(currentdate,INTERVAL 1 DAY);
    END WHILE;
    END
    //
    DELIMITER ;

    TRUNCATE TABLE time_dimension;

    CALL fill_date_dimension('1-01-01','2015-01-01');
    OPTIMIZE TABLE time_dimension;