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; |
Top comments (0)