DEV Community

Cover image for Creating a Date Dimension Table in PostgreSQL
Nicholas Duffy
Nicholas Duffy

Posted on • Originally published at duffn.github.io on

5

Creating a Date Dimension Table in PostgreSQL

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;

Sentry image

See why 4M developers consider Sentry, “not bad.”

Fixing code doesn’t have to be the worst part of your day. Learn how Sentry can help.

Learn more

Top comments (0)

A Workflow Copilot. Tailored to You.

Pieces.app image

Our desktop app, with its intelligent copilot, streamlines coding by generating snippets, extracting code from screenshots, and accelerating problem-solving.

Read the docs

👋 Kindness is contagious

Please leave a ❤️ or a friendly comment on this post if you found it helpful!

Okay