Skip to content

Instantly share code, notes, and snippets.

@Szeliga Szeliga/date_dimenion.sql
Last active Feb 18, 2019

Embed
What would you like to do?
Data warehouse date dimenion script
CREATE SCHEMA IF NOT EXISTS dimensions;
DROP TABLE dimensions.dates;
CREATE TABLE dimensions.dates (
id SERIAL PRIMARY KEY,
date date,
epoch bigint,
day_suffix text,
day_name text,
day_of_week integer,
day_of_month integer,
day_of_quarter integer,
day_of_year integer,
week_of_month integer,
week_of_year integer,
week_of_year_iso text,
month_actual integer,
month_name text,
month_name_abbreviated text,
quarter_actual integer,
quarter_name text,
year_actual integer,
year_iso integer,
first_day_of_week date,
last_day_of_week date,
first_day_of_month date,
last_day_of_month date,
first_day_of_quarter date,
last_day_of_quarter date,
first_day_of_year date,
last_day_of_year date,
mmyyyy text,
mmddyyyy text,
weekend_indr boolean
);
INSERT INTO dimensions.dates (date) VALUES (NULL);
CREATE OR REPLACE FUNCTION insert_date_dimension_row() RETURNS trigger AS
$BODY$
BEGIN
NEW.date := NEW.date;
NEW.epoch := EXTRACT(epoch FROM NEW.date)::bigint;
NEW.day_suffix := TO_CHAR(NEW.date, 'fmDDth');
NEW.day_name := TO_CHAR(NEW.date, 'Day');
NEW.day_of_week := EXTRACT(isodow FROM NEW.date)::int;
NEW.day_of_month := EXTRACT(DAY FROM NEW.date)::int;
NEW.day_of_quarter := NEW.date - DATE_TRUNC('quarter', NEW.date)::date + 1;
NEW.day_of_year := EXTRACT(doy FROM NEW.date)::int;
NEW.week_of_month := TO_CHAR(NEW.date, 'W')::INT;
NEW.week_of_year := EXTRACT(week FROM NEW.date)::int;
NEW.week_of_year_iso := TO_CHAR(NEW.date, 'YYYY"-W"IW-') || EXTRACT(isodow FROM NEW.date);
NEW.month_actual := EXTRACT(MONTH FROM NEW.date)::int;
NEW.month_name := TO_CHAR(NEW.date, 'Month');
NEW.month_name_abbreviated := TO_CHAR(NEW.date, 'Mon');
NEW.quarter_actual := EXTRACT(quarter FROM NEW.date)::int;
NEW.quarter_name := CASE
WHEN EXTRACT(quarter
FROM NEW.date) = 1 THEN 'First'
WHEN EXTRACT(quarter
FROM NEW.date) = 2 THEN 'Second'
WHEN EXTRACT(quarter
FROM NEW.date) = 3 THEN 'Third'
WHEN EXTRACT(quarter
FROM NEW.date) = 4 THEN 'Fourth'
END;
NEW.year_actual := EXTRACT(year FROM NEW.date)::int;
NEW.year_iso := EXTRACT(isoyear FROM NEW.date)::int;
NEW.first_day_of_week := NEW.date +(1 -EXTRACT(isodow FROM NEW.date))::INT;
NEW.last_day_of_week := NEW.date +(7 -EXTRACT(isodow FROM NEW.date))::INT;
NEW.first_day_of_month := NEW.date +(1 -EXTRACT(DAY FROM NEW.date))::INT;
NEW.last_day_of_month := (DATE_TRUNC('MONTH', NEW.date) + INTERVAL '1 MONTH - 1 day')::DATE;
NEW.first_day_of_quarter := DATE_TRUNC('quarter', NEW.date)::DATE;
NEW.last_day_of_quarter := (DATE_TRUNC('quarter', NEW.date) +INTERVAL '3 MONTH - 1 day')::DATE;
NEW.first_day_of_year := TO_DATE(EXTRACT(year FROM NEW.date) || '-01-01', 'YYYY-MM-DD');
NEW.last_day_of_year := TO_DATE(EXTRACT(year FROM NEW.date) || '-12-31', 'YYYY-MM-DD');
NEW.mmyyyy := TO_CHAR(NEW.date, 'mmyyyy');
NEW.mmddyyyy := TO_CHAR(NEW.date, 'mmddyyyy');
NEW.weekend_indr := CASE
WHEN EXTRACT(isodow FROM NEW.date) IN (6, 7) THEN TRUE
ELSE FALSE
END;
RETURN NEW;
END;
$BODY$ LANGUAGE plpgsql;
CREATE TRIGGER "insert_date_dimension_row" BEFORE INSERT ON dimensions.dates
FOR EACH ROW EXECUTE PROCEDURE insert_date_dimension_row();
INSERT INTO dimensions.dates(date) SELECT datum::date AS date
FROM generate_series('2014-01-01', '2050-01-01', interval '1day') AS datum;
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.