Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
MySQL Date Dimension Build Script
/* 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;
###### date table
DROP TABLE IF EXISTS dates;
CREATE TABLE dates (
date_id BIGINT PRIMARY KEY,
date DATE NOT NULL,
timestamp BIGINT NOT NULL,
weekend CHAR(10) NOT NULL DEFAULT "Weekday",
day_of_week CHAR(10) NOT NULL,
month CHAR(10) NOT NULL,
month_day INT NOT NULL,
year INT NOT NULL,
week_starting_monday CHAR(2) NOT NULL,
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( '2010-01-01', INTERVAL number DAY )
FROM numbers
WHERE DATE_ADD( '2010-01-01', INTERVAL number DAY ) BETWEEN '2010-01-01' AND '2020-01-01'
ORDER BY number;
###### fill in other rows
UPDATE dates SET
timestamp = UNIX_TIMESTAMP(date),
day_of_week = DATE_FORMAT( date, "%W" ),
weekend = IF( DATE_FORMAT( date, "%W" ) IN ('Saturday','Sunday'), 'Weekend', 'Weekday'),
month = DATE_FORMAT( date, "%M"),
year = DATE_FORMAT( date, "%Y" ),
month_day = DATE_FORMAT( date, "%d" );
UPDATE dates SET week_starting_monday = DATE_FORMAT(date,'%v');
@johngrimes

This comment has been minimized.

Copy link
Owner Author

johngrimes commented May 21, 2010

Currently creates a date for every day between January 1, 2010 and January 1, 2020.

@ghost

This comment has been minimized.

Copy link

ghost commented Aug 30, 2016

breaks at line 33 with error:
Field 'timestamp' doesn't have a default value

I fixed it: https://gist.github.com/leo90skk/69790149458a8f23767a88f03f2295ba

@sasgoose

This comment has been minimized.

Copy link

sasgoose commented Jan 3, 2019

I needed to add in defaults for all of the not null columns that are not given a value in the insert

@gerontec2

This comment has been minimized.

Copy link

gerontec2 commented Apr 16, 2019

Very nice!
I used Date::Holidays::DE to also mark the local holidays:

#!/usr/bin/perl -w
use Date::Holidays::DE qw(holidays);
#my $feiertage_ref = holidays();
$feiertage_ref = holidays(FORMAT=>"%Y%m%d", YEAR=>2019, WEEKENDS=>1,WHERE=>['common', 'by'] );
#$feiertage_ref = holidays(WEEKENDS=>0);
my @feiertage = @$feiertage_ref;
print "$feiertage_ref\n";
print "@feiertage\n";

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.