Skip to content

Nicholas Duffy

Creating a Date Dimension Table in PostgreSQL

postgresql, databases1 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;
2
3CREATE TABLE d_date
4(
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 NULL
34);
35
36ALTER TABLE public.d_date ADD CONSTRAINT d_date_date_dim_id_pk PRIMARY KEY (date_dim_id);
37
38CREATE INDEX d_date_date_actual_idx
39 ON d_date(date_actual);
40
41COMMIT;
42
43INSERT INTO d_date
44SELECT 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 CASE
61 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 CASE
78 WHEN EXTRACT(ISODOW FROM datum) IN (6, 7) THEN TRUE
79 ELSE FALSE
80 END AS weekend_indr
81FROM (SELECT '1970-01-01'::DATE + SEQUENCE.DAY AS datum
82 FROM GENERATE_SERIES(0, 29219) AS SEQUENCE (DAY)
83 GROUP BY SEQUENCE.DAY) DQ
84ORDER BY 1;
85
86COMMIT;