MySQL Date Dimension Build Script

date.sql

/* Adapted from Tom Cunningham's 'Data Warehousing with MySql' (www.meansandends.com/mysql-data-warehouse) */

###### small-numbers table
DROP TABLE IF EXISTS numbers_small;
CREATE TABLE numbers_small (number INT);
INSERT INTO numbers_small VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

###### main numbers table
DROP TABLE IF EXISTS numbers;
CREATE TABLE numbers (number BIGINT);
INSERT INTO numbers
SELECT thousands.number * 1000 + hundreds.number * 100 + tens.number * 10 + ones.number
  FROM numbers_small thousands, numbers_small hundreds, numbers_small tens, numbers_small ones
LIMIT 1000000;

###### time table
DROP TABLE IF EXISTS times;
CREATE TABLE times (
  time_id              BIGINT PRIMARY KEY,
  time				   TIME,
  hour_12			   INT,
  hour_12_period	   CHAR(2),
  hour				   INT,
  minute			   INT,
  core_hour			   INT,
  standard_day		   INT
);

###### populate it with minutes
INSERT INTO times (time_id, time)
SELECT number, DATE_ADD( '1970-01-01 00:00:00', INTERVAL number MINUTE )
  FROM numbers
  ORDER BY number
  LIMIT 1440;
  
UPDATE times SET
  hour_12 = 		  time_format(time, '%h'),
  hour_12_period =	  time_format(time, '%p'),
  hour =              hour(time),
  minute =            minute(time),
  core_hour = 		  if(hour between 9 and 16,1,0),
  standard_day = 	  1
;

###### date table
/* Additional fields to add later?
isthisweek
isthismonth
isyesterday
ispreviousworkday
ispreviousfullweek
ispreviousfullmonth
*/
DROP TABLE IF EXISTS dates;
CREATE TABLE dates (
  date_id              BIGINT PRIMARY KEY, 
  julian_date          BIGINT,
  date                 DATE NOT NULL,
  timestamp            BIGINT,
  istoday              INT NOT NULL DEFAULT 0,
  isweekend            INT NOT NULL DEFAULT 0,
  isworkday            INT NOT NULL DEFAULT 1,
  weekend_text         CHAR(10) NOT NULL DEFAULT "Weekday",
  day_of_week_full     CHAR(10),
  day_of_week_short    CHAR(3),
  day_of_week          INT,
  month                INT,
  month_full           CHAR(10),
  month_short          CHAR(3),
  first_date_of_month  DATE,
  last_date_of_month   DATE,
  days_in_month        INT,
  day_of_month         INT, 
  day_of_month_text    CHAR(5), 
  quarter              INT,
  year                 INT,
  year_month_number    INT,
  year_week_number     INT,
  day_of_year          INT, 
  week_starting_monday CHAR(2),
  UNIQUE KEY `date` (`date`),
  KEY `year_week` (`year`,`week_starting_monday`)
);

###### populate it with days
INSERT INTO dates (date_id, date)
SELECT number, DATE_ADD( '2012-01-01', INTERVAL number DAY )
  FROM numbers
  WHERE DATE_ADD( '2012-01-01', INTERVAL number DAY ) BETWEEN '2012-01-01' AND '2022-01-01'
  ORDER BY number;

###### fill in other rows
UPDATE dates SET
  timestamp =         UNIX_TIMESTAMP(date),
  julian_date =       TO_DAYS(date),
  day_of_week      =  IF(DATE_FORMAT( date, "%w" ) = 0,7,DATE_FORMAT( date, "%w" )),
  day_of_week_full =  DATE_FORMAT( date, "%W" ),
  day_of_week_short = DATE_FORMAT( date, "%a" ),
  weekend_text =      IF( DATE_FORMAT( date, "%W" ) IN ('Saturday','Sunday'), 'Weekend', 'Weekday'),
  istoday =           IF( date = CURDATE(), 1, 0),
  isweekend =         IF( DATE_FORMAT( date, "%W" ) IN ('Saturday','Sunday'), 1, 0),
  isworkday =         IF( DATE_FORMAT( date, "%W" ) IN ('Saturday','Sunday'), 0, 1),
  month =             DATE_FORMAT( date, "%m"),
  month_full =        DATE_FORMAT( date, "%M"),
  month_short =       DATE_FORMAT( date, "%b"),
  first_date_of_month=date_add(date,interval -DAY(date)+1 DAY),
  last_date_of_month =LAST_DAY(date),
  days_in_month =     DAY(LAST_DAY(date)),
  quarter =           QUARTER( date ),
  year =              DATE_FORMAT( date, "%Y" ),
  year_month_number = CONCAT(DATE_FORMAT(date, "%Y"),DATE_FORMAT(date, "%m")),
  year_week_number =  CONCAT(DATE_FORMAT(date, "%Y"),DATE_FORMAT(date, "%v")),
  day_of_month =      DATE_FORMAT( date, "%d" ),
  day_of_month_text = DATE_FORMAT( date, "%D" ),
  day_of_year =       DATE_FORMAT( date, "%j" );

UPDATE dates SET week_starting_monday = DATE_FORMAT(date,'%v');