Group by Month in PostgreSQL

DATE_TRUNC vs EXTRACT — pick DATE_TRUNC for trends

Two functions can produce a "month" column from a date. EXTRACT(MONTH FROM date) returns the integer 1–12. DATE_TRUNC('month', date) returns a real timestamp pinned to the first day of the month. They look similar; one is the right answer for trends and the other is not.

EXTRACT loses the year. If you GROUP BY EXTRACT(MONTH FROM appl_date), every January from every year collapses into one bucket. On this dataset that is the difference between "78 applications arrive in January" (true across five admission cycles) and a per-cycle trend. It is fine for "what month do we get the most applications, on average?" but wrong for "show me applications per month over time."

DATE_TRUNC keeps the year. DATE_TRUNC('month', appl_date) for 2025-12-15 returns 2025-12-01 00:00:00 — a real timestamp that sorts correctly chronologically and can be plotted on an axis. This is what you want 95% of the time.

-- WRONG for trend reports: EXTRACT collapses years
SELECT EXTRACT(MONTH FROM appl_date) AS month, COUNT(*)
FROM ods_applications
GROUP BY 1
ORDER BY 1;
-- 1 -> 78, 2 -> 7, 3 -> 18 ... every cycle's January mashed together

-- RIGHT: DATE_TRUNC keeps year + month, sorts chronologically
SELECT DATE_TRUNC('month', appl_date) AS month, COUNT(*)
FROM ods_applications
GROUP BY 1
ORDER BY 1;
-- 2021-12-01, 2022-01-01, ... 2026-05-01

The basic monthly grouping pattern

The standard form is one DATE_TRUNC expression in both the SELECT list and the GROUP BY clause. PostgreSQL also lets you reference position (GROUP BY 1) which works fine for short queries, though spelling out the expression is clearer for ones that grow.

You can group by 'week', 'quarter', 'year', 'day', 'hour' — the same pattern, just a different precision string. Quarter is especially useful for academic and fiscal reporting; week is the right granularity for "is this campaign working" questions where daily data is too noisy.

A small gotcha: DATE_TRUNC returns a timestamp even when the input is a date. If your downstream tool wants a date, cast it: DATE_TRUNC('month', appl_date)::date. Most reporting tools accept the timestamp form fine.

-- Applications per month since 2024, with a distinct-applicant check
SELECT
  DATE_TRUNC('month', appl_date)::date AS month,
  COUNT(*)                             AS applications,
  COUNT(DISTINCT appl_applicant)       AS unique_applicants
FROM ods_applications
WHERE appl_date >= '2024-01-01'
GROUP BY DATE_TRUNC('month', appl_date)
ORDER BY month;

Filling missing months with generate_series

A subtle problem with DATE_TRUNC + GROUP BY: months where nothing happened do not appear in the output. Admissions data is seasonal — on this dataset 2025 has applications in January, November and December and nothing in between — so the short query returns three rows for the year, and a chart drawn from it looks like a continuous line when it should show nine zeros.

The fix is generate_series — a PostgreSQL function that produces a list of timestamps at a given interval. Generate every month in your range, LEFT JOIN to your aggregated data, and COALESCE the missing values to zero. The query is longer but the chart is honest.

A practical rule: if your audience is non-technical and the chart will be screenshotted, always fill gaps. If your audience is analysts who will see the underlying data, the cleaner short query is fine.

-- Every month of 2025, with zeros for months that had no applications
SELECT
  m.month,
  COALESCE(a.applications, 0) AS applications
FROM (
  SELECT generate_series(
    DATE_TRUNC('month', '2025-01-01'::date),
    DATE_TRUNC('month', '2025-12-01'::date),
    INTERVAL '1 month'
  )::date AS month
) m
LEFT JOIN (
  SELECT DATE_TRUNC('month', appl_date)::date AS month, COUNT(*) AS applications
  FROM ods_applications
  GROUP BY 1
) a
  ON a.month = m.month
ORDER BY m.month;

-- 2025-01-01  22
-- 2025-02-01   0   <- nine of these before November
-- ...
-- 2025-11-01   9
-- 2025-12-01  21

Pairing DATE_TRUNC with FILTER for funnel reports

Time-series and conditional aggregation pair beautifully. The result is a funnel report: one row per month, columns showing how many applications were submitted, accepted, denied, and deposited. This is the shape of every monthly admissions review.

On ods_applications the admit decision lives in appl_admit_status (AC accepted, DN denied, NEW undecided) and the applicant's progress in appl_current_status, where DP is deposited and MS is "moved to student" — the ones who actually enrolled. PostgreSQL's FILTER (WHERE ...) clause is the cleanest way to write the conditional counts. The portable equivalent is COUNT(CASE WHEN ... THEN 1 END) — same result, slightly more verbose.

A note on rolling metrics: if you need a three-month rolling average rather than a per-month snapshot, you want a window function with a frame clause (OVER (ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)). DATE_TRUNC handles the bucketing; the window handles the smoothing.

-- Admissions funnel for the current cycle: per month, by stage
SELECT
  DATE_TRUNC('month', appl_date)::date                        AS month,
  COUNT(*)                                                    AS submitted,
  COUNT(*) FILTER (WHERE appl_admit_status = 'AC')            AS accepted,
  COUNT(*) FILTER (WHERE appl_admit_status = 'DN')            AS denied,
  COUNT(*) FILTER (WHERE appl_current_status IN ('DP', 'MS')) AS deposited
FROM ods_applications
WHERE appl_date >= '2025-11-01'
GROUP BY 1
ORDER BY month;

Common pitfalls

Time zones bite. DATE_TRUNC('month', some_timestamptz) truncates in the session's time zone, which is usually UTC on Supabase / Postgres servers. If your business operates in US Pacific time, an event at 2025-08-01 02:00 UTC is still July there. ods_appl_status_history.appl_status_date is a plain timestamp (no zone attached), so the honest version makes the stored zone explicit with AT TIME ZONE 'UTC', then converts to the business zone, then truncates.

Off-by-one boundaries. DATE_TRUNC pins to the start of the period. DATE_TRUNC('month', '2025-09-30'::timestamp) is 2025-09-01 00:00:00. Cast a bare string literal like that one: without the ::timestamp (or ::date) PostgreSQL cannot tell which DATE_TRUNC you mean and stops with "function date_trunc(unknown, unknown) is not unique". If you want the end of the month for a label, add an interval: DATE_TRUNC('month', d) + INTERVAL '1 month' - INTERVAL '1 day'.

Ordering by the bucket column always works because DATE_TRUNC returns a real timestamp. If you formatted to a string with TO_CHAR for display ("2025-09"), make sure to ORDER BY the underlying DATE_TRUNC output, not the formatted string — string ordering does the right thing for ISO-format months but not for any other format.

-- Time-zone-aware: state the stored zone, convert, then truncate
SELECT
  DATE_TRUNC(
    'month',
    (appl_status_date AT TIME ZONE 'UTC') AT TIME ZONE 'America/Los_Angeles'
  )::date AS month,
  COUNT(*) AS status_changes
FROM ods_appl_status_history
GROUP BY 1
ORDER BY month;

Practice these on QueryU

Three challenges test the date-bucketing patterns above on the same applications and completions data. Each is graded on its result set — any valid query returning the right rows passes.