Creating a Date Dimension Table in PostgreSQL

#postgres #database

If you’ve ever read any of Ralph Kimball’s data warehouse books or have any experience with modeling data in the Kimball style, you know that a date dimension is a key part of any star schema. You probably found resources for creating Oracle, SQL Server or MySQL but may have had difficulty finding SQL to create a date dimension table for PostgreSQL. Here’s a bit of code adapted from the PostgreSQL wiki that I like for creating the ever necessary date dimension 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, 'TMDay') 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, 'TMMonth') 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(YEAR 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;