— postgresql, databases — 1 min read
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.
1DROP TABLE if exists d_date;23CREATE TABLE d_date4(5 date_dim_id INT NOT NULL,6 date_actual DATE NOT NULL,7 epoch BIGINT NOT NULL,8 day_suffix VARCHAR(4) NOT NULL,9 day_name VARCHAR(9) NOT NULL,10 day_of_week INT NOT NULL,11 day_of_month INT NOT NULL,12 day_of_quarter INT NOT NULL,13 day_of_year INT NOT NULL,14 week_of_month INT NOT NULL,15 week_of_year INT NOT NULL,16 week_of_year_iso CHAR(10) NOT NULL,17 month_actual INT NOT NULL,18 month_name VARCHAR(9) NOT NULL,19 month_name_abbreviated CHAR(3) NOT NULL,20 quarter_actual INT NOT NULL,21 quarter_name VARCHAR(9) NOT NULL,22 year_actual INT NOT NULL,23 first_day_of_week DATE NOT NULL,24 last_day_of_week DATE NOT NULL,25 first_day_of_month DATE NOT NULL,26 last_day_of_month DATE NOT NULL,27 first_day_of_quarter DATE NOT NULL,28 last_day_of_quarter DATE NOT NULL,29 first_day_of_year DATE NOT NULL,30 last_day_of_year DATE NOT NULL,31 mmyyyy CHAR(6) NOT NULL,32 mmddyyyy CHAR(10) NOT NULL,33 weekend_indr BOOLEAN NOT NULL34);3536ALTER TABLE public.d_date ADD CONSTRAINT d_date_date_dim_id_pk PRIMARY KEY (date_dim_id);3738CREATE INDEX d_date_date_actual_idx39 ON d_date(date_actual);4041COMMIT;4243INSERT INTO d_date44SELECT TO_CHAR(datum, 'yyyymmdd')::INT AS date_dim_id,45 datum AS date_actual,46 EXTRACT(EPOCH FROM datum) AS epoch,47 TO_CHAR(datum, 'fmDDth') AS day_suffix,48 TO_CHAR(datum, 'Day') AS day_name,49 EXTRACT(ISODOW FROM datum) AS day_of_week,50 EXTRACT(DAY FROM datum) AS day_of_month,51 datum - DATE_TRUNC('quarter', datum)::DATE + 1 AS day_of_quarter,52 EXTRACT(DOY FROM datum) AS day_of_year,53 TO_CHAR(datum, 'W')::INT AS week_of_month,54 EXTRACT(WEEK FROM datum) AS week_of_year,55 EXTRACT(ISOYEAR FROM datum) || TO_CHAR(datum, '"-W"IW-') || EXTRACT(ISODOW FROM datum) AS week_of_year_iso,56 EXTRACT(MONTH FROM datum) AS month_actual,57 TO_CHAR(datum, 'Month') AS month_name,58 TO_CHAR(datum, 'Mon') AS month_name_abbreviated,59 EXTRACT(QUARTER FROM datum) AS quarter_actual,60 CASE61 WHEN EXTRACT(QUARTER FROM datum) = 1 THEN 'First'62 WHEN EXTRACT(QUARTER FROM datum) = 2 THEN 'Second'63 WHEN EXTRACT(QUARTER FROM datum) = 3 THEN 'Third'64 WHEN EXTRACT(QUARTER FROM datum) = 4 THEN 'Fourth'65 END AS quarter_name,66 EXTRACT(ISOYEAR FROM datum) AS year_actual,67 datum + (1 - EXTRACT(ISODOW FROM datum))::INT AS first_day_of_week,68 datum + (7 - EXTRACT(ISODOW FROM datum))::INT AS last_day_of_week,69 datum + (1 - EXTRACT(DAY FROM datum))::INT AS first_day_of_month,70 (DATE_TRUNC('MONTH', datum) + INTERVAL '1 MONTH - 1 day')::DATE AS last_day_of_month,71 DATE_TRUNC('quarter', datum)::DATE AS first_day_of_quarter,72 (DATE_TRUNC('quarter', datum) + INTERVAL '3 MONTH - 1 day')::DATE AS last_day_of_quarter,73 TO_DATE(EXTRACT(YEAR FROM datum) || '-01-01', 'YYYY-MM-DD') AS first_day_of_year,74 TO_DATE(EXTRACT(YEAR FROM datum) || '-12-31', 'YYYY-MM-DD') AS last_day_of_year,75 TO_CHAR(datum, 'mmyyyy') AS mmyyyy,76 TO_CHAR(datum, 'mmddyyyy') AS mmddyyyy,77 CASE78 WHEN EXTRACT(ISODOW FROM datum) IN (6, 7) THEN TRUE79 ELSE FALSE80 END AS weekend_indr81FROM (SELECT '1970-01-01'::DATE + SEQUENCE.DAY AS datum82 FROM GENERATE_SERIES(0, 29219) AS SEQUENCE (DAY)83 GROUP BY SEQUENCE.DAY) DQ84ORDER BY 1;8586COMMIT;