How to Build a Date Dimension Table in SQL

Every time-series chart you've built has this bug: days with no activity don't show up. Your revenue chart jumps from March 3 to March 7 because nobody bought anything on the 4th, 5th, or 6th. The chart doesn't show $0 for those days. It skips them, and a flat week looks like steady growth.

The fix is a date dimension table. One table with a row for every calendar date, pre-populated with day of week, month name, quarter, fiscal year, weekend flags, and week numbers. LEFT JOIN your sparse fact data against it, COALESCE the nulls to zero, and the gaps appear.

If you've worked with a Kimball-style warehouse, none of this is new. But most date dimension tutorials either hand you a 200-column monster or a three-line generate_series with no useful columns. Here's the version I actually use.

The complete table (Postgres)

Copy this, run it, you're done. Every date from 2020 through 2030, with every column I've needed in practice.

CREATE TABLE dim_date AS
WITH dates AS (
  SELECT d::date AS date_day
  FROM generate_series(
    '2020-01-01'::date,
    '2030-12-31'::date,
    '1 day'
  ) AS d
)
SELECT
  date_day,
  EXTRACT(DOW FROM date_day)::int              AS day_of_week_sunday,   -- 0=Sun .. 6=Sat
  EXTRACT(ISODOW FROM date_day)::int           AS day_of_week_monday,   -- 1=Mon .. 7=Sun (ISO)
  TRIM(TO_CHAR(date_day, 'Day'))               AS day_name,
  EXTRACT(DAY FROM date_day)::int              AS day_of_month,
  EXTRACT(DOY FROM date_day)::int              AS day_of_year,
  EXTRACT(MONTH FROM date_day)::int            AS month_number,
  TRIM(TO_CHAR(date_day, 'Month'))             AS month_name,
  EXTRACT(QUARTER FROM date_day)::int          AS calendar_quarter,
  EXTRACT(YEAR FROM date_day)::int             AS year,
 
  -- ISO 8601 week: Monday start, week 1 contains the year's first Thursday
  EXTRACT(WEEK FROM date_day)::int             AS iso_week,
  EXTRACT(ISOYEAR FROM date_day)::int          AS iso_year,
 
  -- US week: Sunday start, week containing Jan 1 is week 1
  FLOOR((EXTRACT(DOY FROM date_day)::int +
         EXTRACT(DOW FROM DATE_TRUNC('year', date_day))::int - 1)
        / 7.0)::int + 1                        AS us_week,
 
  EXTRACT(DOW FROM date_day) IN (0, 6)         AS is_weekend,
 
  -- Fiscal year starting April 1 (UK, India, Canada)
  CASE WHEN EXTRACT(MONTH FROM date_day) >= 4
       THEN EXTRACT(YEAR FROM date_day)::int
       ELSE EXTRACT(YEAR FROM date_day)::int - 1
  END                                           AS fiscal_year_apr,
  MOD(EXTRACT(MONTH FROM date_day)::int - 4 + 12, 12) / 3 + 1
                                                AS fiscal_quarter_apr,
 
  -- Fiscal year starting October 1 (US federal)
  CASE WHEN EXTRACT(MONTH FROM date_day) >= 10
       THEN EXTRACT(YEAR FROM date_day)::int + 1
       ELSE EXTRACT(YEAR FROM date_day)::int
  END                                           AS fiscal_year_oct,
  MOD(EXTRACT(MONTH FROM date_day)::int - 10 + 12, 12) / 3 + 1
                                                AS fiscal_quarter_oct
 
FROM dates;
 
CREATE INDEX idx_dim_date_day ON dim_date (date_day);

That's 4,018 rows, about 50ms to create. A few things to notice.

The TRIM() around TO_CHAR output matters. Postgres pads day and month names with trailing spaces to a fixed width ('Monday ', 'January '). Skip the trim and your GROUP BY on month_name will work, but your chart labels will have weird trailing whitespace.

Fiscal quarter math. The MOD trick shifts the month number so that the fiscal year's first month maps to quarter 1. For an April start: month 4 maps to Q1, month 7 to Q2, and so on through month 3 mapping to Q4. If your fiscal year starts in a different month, change the offset inside MOD to match. The formula is always MOD(month - start_month + 12, 12) / 3 + 1 with integer division.

The week numbering gotcha

ISO 8601 and US week numbering disagree on two things: which day starts the week (Monday vs Sunday) and what counts as week 1.

ISO says week 1 is the week containing the year's first Thursday. That means Dec 31 can land in week 1 of the next year, and Jan 1 can be in week 52 or 53 of the previous year. The query includes iso_year as a separate column for exactly this reason. If you group by year, iso_week instead of iso_year, iso_week, you'll double-count some weeks at year boundaries. I've shipped this bug.

The US convention is simpler: the week containing January 1 is always week 1, weeks start on Sunday. The formula in the query handles this, though I won't pretend it's readable. If you're unsure which convention your company uses, check payroll. Payroll software usually decides for you.

Holidays: use a lookup table

Don't try to calculate Easter in SQL. Just don't. Holiday logic belongs in a lookup table that someone populates from a static CSV once a year.

CREATE TABLE dim_holiday (
  date_day     DATE PRIMARY KEY,
  holiday_name TEXT NOT NULL,
  country_code CHAR(2) NOT NULL DEFAULT 'US'
);
 
INSERT INTO dim_holiday (date_day, holiday_name) VALUES
  ('2026-01-01', 'New Year''s Day'),
  ('2026-01-19', 'MLK Day'),
  ('2026-02-16', 'Presidents'' Day'),
  ('2026-05-25', 'Memorial Day'),
  ('2026-07-04', 'Independence Day'),
  ('2026-09-07', 'Labor Day'),
  ('2026-11-26', 'Thanksgiving'),
  ('2026-12-25', 'Christmas Day');
-- Repeat for each year you need.

Then join it into your date dimension:

CREATE OR REPLACE VIEW dim_date_full AS
SELECT d.*, h.holiday_name IS NOT NULL AS is_holiday, h.holiday_name
FROM dim_date d
LEFT JOIN dim_holiday h ON d.date_day = h.date_day;

A view here is the right call. The holiday table changes once a year; no point duplicating data into dim_date when a join is instantaneous against 4,000 rows.

DuckDB variant

DuckDB replaces generate_series with range() and has cleaner function names. The main structural difference: range() excludes the upper bound, so the end date is 2031-01-01 instead of 2030-12-31.

CREATE TABLE dim_date AS
SELECT
  d::date                                       AS date_day,
  DAYOFWEEK(d::date)                            AS day_of_week_sunday,  -- 0=Sun .. 6=Sat
  EXTRACT(ISODOW FROM d::date)::int             AS day_of_week_monday,  -- 1=Mon .. 7=Sun
  DAYNAME(d::date)                              AS day_name,
  DAY(d::date)                                  AS day_of_month,
  DAYOFYEAR(d::date)                            AS day_of_year,
  MONTH(d::date)                                AS month_number,
  MONTHNAME(d::date)                            AS month_name,
  QUARTER(d::date)                              AS calendar_quarter,
  YEAR(d::date)                                 AS year,
  WEEKOFYEAR(d::date)                           AS iso_week,
  DAYOFWEEK(d::date) IN (0, 6)                  AS is_weekend,
  CASE WHEN MONTH(d::date) >= 4
       THEN YEAR(d::date) ELSE YEAR(d::date) - 1
  END                                           AS fiscal_year_apr,
  CASE WHEN MONTH(d::date) >= 10
       THEN YEAR(d::date) + 1 ELSE YEAR(d::date)
  END                                           AS fiscal_year_oct
FROM range(DATE '2020-01-01', DATE '2031-01-01', INTERVAL 1 DAY) t(d);

DuckDB doesn't pad string output, so you skip the TRIM() workaround. The function names (DAYNAME, MONTHNAME, WEEKOFYEAR) are also more readable than Postgres's EXTRACT(x FROM y) plus TO_CHAR pattern. If you're running analytics locally or in Fastero's DuckDB store, this is the version to use.

Filling date gaps with LEFT JOIN

This is the whole point. Say you're charting daily revenue from an orders table:

SELECT
  d.date_day,
  d.day_name,
  d.is_weekend,
  COALESCE(SUM(o.amount_cents), 0) / 100.0 AS revenue
FROM dim_date d
LEFT JOIN orders o ON d.date_day = o.created_at::date
WHERE d.date_day BETWEEN '2026-01-01' AND '2026-06-30'
GROUP BY d.date_day, d.day_name, d.is_weekend
ORDER BY d.date_day;

Without the date dimension, days with zero orders vanish from results. Your charting library connects March 3 directly to March 7, drawing a line through empty space as if revenue existed on those days. With the LEFT JOIN, March 4 through 6 show up as $0.00, and the chart renders the flat period accurately.

This applies to any sparse time-series: daily active users, support tickets, deployments, error counts. "Nothing happened" is data. Skipping it is a lie your chart tells.

View vs materialized table

Three options, quick take on each.

CREATE TABLE (the queries above) gives fastest reads and supports indexes. Static, but dates don't change, so "static" isn't a downside here. If you extend the range, DROP and recreate. Takes 50ms.

CREATE VIEW wrapping the generate_series CTE recalculates on every query. For 4,000 rows that's free. You can't index it, but you also never have a stale artifact sitting around.

Materialized view gets you physical storage with indexes, plus REFRESH MATERIALIZED VIEW dim_date when you want to rebuild. On Postgres 9.4+ this is the textbook answer, though for a date dimension the advantage over a plain table is mostly philosophical. Set the range to 2050 and forget about it either way.

I default to a plain table. It's the simplest thing that works, and nobody has ever paged me because a date dimension went stale.

Date dimensions and dashboards

A date dimension is one of those 10-minute setups that prevents subtle charting bugs for years. The generate_series spine in Postgres (or range() in DuckDB), fiscal columns, holiday flags, and the LEFT JOIN pattern together eliminate the "no data does not equal zero" problem from every time-series query you write.

If you're building live dashboards from SQL, this table is the foundation. Every date-axis widget in Fastero benefits from it: the query returns a continuous date series, and the chart renders without gaps or misleading interpolation.

Try Fastero free — connect your database and build time-series dashboards with no date gaps. No credit card required.