A date dimension holds one row per day with the attributes reports group by, computed once instead of in every query. generate_series builds BookNest's for 2025 and 2026; Dimensional Modeling joins it to the sales facts:
DROP TABLE IF EXISTS mart.dim_date;
CREATE TABLE mart.dim_date AS
SELECT to_char(d, 'YYYYMMDD')::int AS date_key,
d::date AS full_date,
to_char(d, 'FMDay') AS day_name,
extract(isodow FROM d) IN (6, 7) AS is_weekend,
extract(week FROM d)::int AS iso_week,
extract(isoyear FROM d)::int AS iso_year,
to_char(d, 'FMMonth') AS month_name,
extract(quarter FROM d)::int AS quarter,
extract(year FROM d)::int AS year,
d = date_trunc('month', d) + interval '1 month - 1 day' AS is_month_end
FROM generate_series(date '2025-01-01', date '2026-12-31', interval '1 day') AS d;
ALTER TABLE mart.dim_date ADD PRIMARY KEY (date_key);
SELECT date_key, day_name, is_weekend, iso_week, iso_year, quarter, is_month_end
FROM mart.dim_date WHERE full_date BETWEEN '2025-12-28' AND '2026-01-01';Output
date_key | day_name | is_weekend | iso_week | iso_year | quarter | is_month_end ----------+-----------+------------+----------+----------+---------+-------------- 20251228 | Sunday | t | 52 | 2025 | 4 | f 20251229 | Monday | f | 1 | 2026 | 4 | f 20251230 | Tuesday | f | 1 | 2026 | 4 | f 20251231 | Wednesday | f | 1 | 2026 | 4 | t 20260101 | Thursday | f | 1 | 2026 | 1 | f
The table has 730 rows, and the year boundary shows why it pays: 29 December 2025 is in ISO week 1 of 2026, so weekly reports group by iso_year and iso_week, never year. The integer YYYYMMDD key is readable, compact and ordered. Add holidays or fiscal periods as needed, and load years ahead so new orders never miss a row.