Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save kharysharpe/7c4c270b7b77afaafa2c067da2eefbb0 to your computer and use it in GitHub Desktop.
Save kharysharpe/7c4c270b7b77afaafa2c067da2eefbb0 to your computer and use it in GitHub Desktop.
Creating a date dimension table in PostgreSQL
DROP TABLE if exists d_date;
CREATE TABLE d_date
(
date_dim_id INT NOT NULL,
date_actual DATE NOT NULL,
epoch BIGINT NOT NULL,
day_suffix VARCHAR(4) NOT NULL,
day_name VARCHAR(9) NOT NULL,
day_of_week INT NOT NULL,
day_of_month INT NOT NULL,
day_of_quarter INT NOT NULL,
day_of_year INT NOT NULL,
week_of_month INT NOT NULL,
week_of_year INT NOT NULL,
week_of_year_iso CHAR(10) NOT NULL,
month_actual INT NOT NULL,
month_name VARCHAR(9) NOT NULL,
month_name_abbreviated CHAR(3) NOT NULL,
quarter_actual INT NOT NULL,
quarter_name VARCHAR(9) NOT NULL,
year_actual INT NOT NULL,
first_day_of_week DATE NOT NULL,
last_day_of_week DATE NOT NULL,
first_day_of_month DATE NOT NULL,
last_day_of_month DATE NOT NULL,
first_day_of_quarter DATE NOT NULL,
last_day_of_quarter DATE NOT NULL,
first_day_of_year DATE NOT NULL,
last_day_of_year DATE NOT NULL,
mmyyyy CHAR(6) NOT NULL,
mmddyyyy CHAR(10) NOT NULL,
weekend_indr BOOLEAN NOT NULL
);
ALTER TABLE public.d_date ADD CONSTRAINT d_date_date_dim_id_pk PRIMARY KEY (date_dim_id);
CREATE INDEX d_date_date_actual_idx
ON d_date(date_actual);
COMMIT;
INSERT INTO d_date
SELECT TO_CHAR(datum, 'yyyymmdd')::INT AS date_dim_id,
datum AS date_actual,
EXTRACT(EPOCH FROM datum) AS epoch,
TO_CHAR(datum, 'fmDDth') AS day_suffix,
TO_CHAR(datum, 'Day') AS day_name,
EXTRACT(ISODOW FROM datum) AS day_of_week,
EXTRACT(DAY FROM datum) AS day_of_month,
datum - DATE_TRUNC('quarter', datum)::DATE + 1 AS day_of_quarter,
EXTRACT(DOY FROM datum) AS day_of_year,
TO_CHAR(datum, 'W')::INT AS week_of_month,
EXTRACT(WEEK FROM datum) AS week_of_year,
EXTRACT(ISOYEAR FROM datum) || TO_CHAR(datum, '"-W"IW-') || EXTRACT(ISODOW FROM datum) AS week_of_year_iso,
EXTRACT(MONTH FROM datum) AS month_actual,
TO_CHAR(datum, 'Month') AS month_name,
TO_CHAR(datum, 'Mon') AS month_name_abbreviated,
EXTRACT(QUARTER FROM datum) AS quarter_actual,
CASE
WHEN EXTRACT(QUARTER FROM datum) = 1 THEN 'First'
WHEN EXTRACT(QUARTER FROM datum) = 2 THEN 'Second'
WHEN EXTRACT(QUARTER FROM datum) = 3 THEN 'Third'
WHEN EXTRACT(QUARTER FROM datum) = 4 THEN 'Fourth'
END AS quarter_name,
EXTRACT(ISOYEAR FROM datum) AS year_actual,
datum + (1 - EXTRACT(ISODOW FROM datum))::INT AS first_day_of_week,
datum + (7 - EXTRACT(ISODOW FROM datum))::INT AS last_day_of_week,
datum + (1 - EXTRACT(DAY FROM datum))::INT AS first_day_of_month,
(DATE_TRUNC('MONTH', datum) + INTERVAL '1 MONTH - 1 day')::DATE AS last_day_of_month,
DATE_TRUNC('quarter', datum)::DATE AS first_day_of_quarter,
(DATE_TRUNC('quarter', datum) + INTERVAL '3 MONTH - 1 day')::DATE AS last_day_of_quarter,
TO_DATE(EXTRACT(YEAR FROM datum) || '-01-01', 'YYYY-MM-DD') AS first_day_of_year,
TO_DATE(EXTRACT(YEAR FROM datum) || '-12-31', 'YYYY-MM-DD') AS last_day_of_year,
TO_CHAR(datum, 'mmyyyy') AS mmyyyy,
TO_CHAR(datum, 'mmddyyyy') AS mmddyyyy,
CASE
WHEN EXTRACT(ISODOW FROM datum) IN (6, 7) THEN TRUE
ELSE FALSE
END AS weekend_indr
FROM (SELECT '1970-01-01'::DATE + SEQUENCE.DAY AS datum
FROM GENERATE_SERIES(0, 29219) AS SEQUENCE (DAY)
GROUP BY SEQUENCE.DAY) DQ
ORDER BY 1;
COMMIT;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment