VDm9LUj5

1/* Adapted from Tom Cunningham's 'Data Warehousing with MySql' (www.meansandends.com/mysql-data-warehouse) */
2
3###### small-numbers table
4DROP TABLE IF EXISTS numbers_small;
5CREATE TABLE numbers_small (number INT);
6INSERT INTO numbers_small VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
7
8###### main numbers table
9DROP TABLE IF EXISTS numbers;
10CREATE TABLE numbers (number BIGINT);
11INSERT INTO numbers
12SELECT thousands.number * 1000 + hundreds.number * 100 + tens.number * 10 + ones.number
13  FROM numbers_small thousands, numbers_small hundreds, numbers_small tens, numbers_small ones
14LIMIT 1000000;
15
16###### date table
17DROP TABLE IF EXISTS dates;
18CREATE TABLE dates (
19  date_id          BIGINT PRIMARY KEY, 
20  date             DATE NOT NULL,
21  timestamp        BIGINT NOT NULL, 
22  weekend          CHAR(10) NOT NULL DEFAULT "Weekday",
23  day_of_week      CHAR(10) NOT NULL,
24  month            CHAR(10) NOT NULL,
25  month_day        INT NOT NULL, 
26  year             INT NOT NULL,
27  week_starting_monday CHAR(2) NOT NULL,
28  UNIQUE KEY `date` (`date`),
29  KEY `year_week` (`year`,`week_starting_monday`)
30);
31
32###### populate it ...
33INSERT INTO dates (date_id, date, timestamp, weekend, day_of_week, month, month_day, year, week_starting_monday)
34SELECT
35    date_id,
36    date,
37    UNIX_TIMESTAMP(date) AS timestamp,
38    IF( DATE_FORMAT( date, "%W" ) IN ('Saturday','Sunday'), 'Weekend', 'Weekday') AS weekend,
39    DATE_FORMAT( date, "%W" ) AS day_of_week,
40    DATE_FORMAT( date, "%M") AS month,
41    DATE_FORMAT( date, "%d" ) AS month_day,
42    DATE_FORMAT( date, "%Y" ) AS year,
43    DATE_FORMAT(date,'%v') AS week_starting_monday
44FROM (
45    SELECT
46        number AS date_id,
47        DATE_ADD( '2010-01-01', INTERVAL number DAY ) AS date
48    FROM numbers
49    WHERE DATE_ADD( '2010-01-01', INTERVAL number DAY ) BETWEEN '2010-01-01' AND '2020-01-01'
50) AS t0
51ORDER BY date_id;