Building a Date Dimension Table in PostgreSQL

Developer & creator of pgref.dev · 8 min read

generate_seriesreportingdate-dimension

Every reporting database needs a calendar table. Here's one you can create in PostgreSQL with a single query, no external tools, no Python scripts generating CSVs.

Why you need a calendar table

Three problems it solves: time-series reports that skip dates (days with no orders simply don't appear in a GROUP BY), fiscal period calculations (which quarter is July in a July-June fiscal year?), and business-day filtering. You do the work once in the calendar table, then it's just a join.

The CREATE TABLE

CREATE TABLE dim_date (
  date_key        INT PRIMARY KEY,       -- YYYYMMDD integer
  full_date       DATE NOT NULL UNIQUE,
  year            SMALLINT NOT NULL,
  quarter         SMALLINT NOT NULL,     -- 1-4
  month           SMALLINT NOT NULL,     -- 1-12
  month_name      TEXT NOT NULL,
  week_of_year    SMALLINT NOT NULL,     -- ISO week number
  day_of_month    SMALLINT NOT NULL,
  day_of_week     SMALLINT NOT NULL,     -- 1=Monday (ISO), 7=Sunday
  day_name        TEXT NOT NULL,
  day_of_year     SMALLINT NOT NULL,
  is_weekend      BOOLEAN NOT NULL,
  is_leap_year    BOOLEAN NOT NULL,
  fiscal_quarter  SMALLINT NOT NULL,
  first_of_month  DATE NOT NULL,
  last_of_month   DATE NOT NULL
);

The query

-- Set your fiscal year start month (7 = July for a July-June fiscal year)
-- Change to 1 if your fiscal year matches the calendar year
WITH fiscal_config AS (
  SELECT 7 AS start_month
)
INSERT INTO dim_date
SELECT
  to_char(d, 'YYYYMMDD')::INT                          AS date_key,
  d::DATE                                               AS full_date,
  EXTRACT(YEAR FROM d)::SMALLINT                        AS year,
  EXTRACT(QUARTER FROM d)::SMALLINT                     AS quarter,
  EXTRACT(MONTH FROM d)::SMALLINT                       AS month,
  to_char(d, 'Month')                                   AS month_name,
  EXTRACT(WEEK FROM d)::SMALLINT                        AS week_of_year,
  EXTRACT(DAY FROM d)::SMALLINT                         AS day_of_month,
  EXTRACT(ISODOW FROM d)::SMALLINT                      AS day_of_week,
  to_char(d, 'Day')                                     AS day_name,
  EXTRACT(DOY FROM d)::SMALLINT                         AS day_of_year,
  EXTRACT(ISODOW FROM d) IN (6, 7)                      AS is_weekend,
  (
    EXTRACT(YEAR FROM d)::INT % 4 = 0
    AND (
      EXTRACT(YEAR FROM d)::INT % 100 != 0
      OR EXTRACT(YEAR FROM d)::INT % 400 = 0
    )
  )                                                     AS is_leap_year,
  (
    CASE
      WHEN EXTRACT(MONTH FROM d) >= fc.start_month
        THEN CEIL((EXTRACT(MONTH FROM d) - fc.start_month + 1)::NUMERIC / 3)
      ELSE CEIL((EXTRACT(MONTH FROM d) + 12 - fc.start_month + 1)::NUMERIC / 3)
    END
  )::SMALLINT                                           AS fiscal_quarter,
  date_trunc('month', d)::DATE                          AS first_of_month,
  (date_trunc('month', d) + INTERVAL '1 month'
    - INTERVAL '1 day')::DATE                           AS last_of_month
FROM generate_series(
  '2015-01-01'::TIMESTAMP,
  '2035-12-31'::TIMESTAMP,
  '1 day'::INTERVAL
) AS d,
fiscal_config fc;

Adjust the date range to fit your data. 20 years generates 7,305 rows — the table will be under 1MB.

Sample output:

 date_key | full_date  | year | quarter | month | week | is_weekend | fiscal_q
----------+------------+------+---------+-------+------+------------+----------
 20150101 | 2015-01-01 | 2015 |       1 |     1 |    1 | f          |        3
 20150102 | 2015-01-02 | 2015 |       1 |     1 |    1 | f          |        3
 20150103 | 2015-01-03 | 2015 |       1 |     1 |    1 | t          |        3

Making it useful

-- Primary access pattern: join on full_date
CREATE INDEX idx_dim_date_full_date ON dim_date (full_date);

-- For business-day-only queries
CREATE INDEX idx_dim_date_business_days ON dim_date (full_date)
WHERE is_weekend = false;

-- For fiscal period rollups
CREATE INDEX idx_dim_date_fiscal ON dim_date (fiscal_quarter, year);

The date gap problem, solved:

-- Without calendar table: missing days with no orders
SELECT created_at::date AS day, SUM(total_amount) AS revenue
FROM orders
WHERE created_at >= '2024-01-01'
GROUP BY 1
ORDER BY 1;
-- Result: Jan 1, Jan 2, Jan 4 ... Jan 3 is missing

-- With calendar table: every day appears
SELECT
  d.full_date AS day,
  COALESCE(SUM(o.total_amount), 0) AS revenue
FROM dim_date d
LEFT JOIN orders o
  ON o.created_at::date = d.full_date
  AND o.created_at >= '2024-01-01'
  AND o.created_at < '2024-02-01'
WHERE d.full_date BETWEEN '2024-01-01' AND '2024-01-31'
GROUP BY d.full_date
ORDER BY d.full_date;

Revenue by fiscal quarter:

SELECT
  d.year,
  d.fiscal_quarter,
  SUM(o.total_amount) AS revenue
FROM dim_date d
LEFT JOIN orders o ON o.created_at::date = d.full_date
GROUP BY d.year, d.fiscal_quarter
ORDER BY d.year, d.fiscal_quarter;

No CASE statements, no date arithmetic, no "what quarter is July again" confusion.

Customisation

Adding holidays: Create a separate public_holidays table and join against it, or add an is_public_holiday boolean column and populate it with a list. For fixed holidays (Christmas, New Year) a CASE statement works; for variable holidays (Easter, Thanksgiving) use a lookup table.

Hour-level granularity: Replace the 1 day interval with 1 hour and add hour columns. A full year at hour level is 8,760 rows — still tiny.

Pay periods: Add pay_period_number based on your payroll cycle. Biweekly pay periods: FLOOR((day_of_year - 1) / 14) + 1.

I use a version of this in every reporting project. It takes 30 seconds to create and saves hours of awkward date arithmetic over the lifetime of a database. The generate_series(), date_trunc(), and to_char() function pages on pgref.dev cover the functions used here if anything needs clarifying.