PostgreSQL date_trunc: Time Buckets Without Breaking Indexes

date_trunc rounds a timestamp down to a chosen boundary: hour, day, week, month, quarter, or year. It is ideal for grouping events into reporting buckets. The important rule is where you use it: truncate the value you display or group by, but use a plain timestamp range to filter an indexed timestamp column.

Build a Daily Report

SELECT
  date_trunc('day', created_at) AS day,
  COUNT(*) AS signups
FROM users
WHERE created_at >= TIMESTAMPTZ '2026-07-01 00:00:00+00'
  AND created_at <  TIMESTAMPTZ '2026-08-01 00:00:00+00'
GROUP BY 1
ORDER BY 1;

GROUP BY 1 means “group by the first selected expression.” It keeps the query readable when the expression is long.

The Index Trap

This query is convenient but often prevents PostgreSQL from using a normal B-tree index on created_at:

-- Avoid for large tables
SELECT COUNT(*)
FROM users
WHERE date_trunc('day', created_at) = DATE '2026-07-13';

Use a half-open range instead. It includes every instant in the day without relying on an artificial 23:59:59.999 endpoint.

-- Index-friendly
SELECT COUNT(*)
FROM users
WHERE created_at >= TIMESTAMPTZ '2026-07-13 00:00:00+00'
  AND created_at <  TIMESTAMPTZ '2026-07-14 00:00:00+00';

Back this with CREATE INDEX users_created_at_idx ON users (created_at) when the table is large and this filter is common.

Weeks Start on Monday

PostgreSQL follows ISO weeks. date_trunc('week', ...) returns Monday at midnight, not Sunday.

SELECT
  date_trunc('week', created_at)::date AS week_start,
  COUNT(*) AS orders
FROM orders
GROUP BY 1
ORDER BY 1;

If a business defines weeks differently, calculate a custom boundary explicitly rather than assuming the database uses a regional convention.

Time Zones: Convert Before You Bucket

A timestamptz is stored as an absolute instant. A “day” for a customer in São Paulo is not the same boundary as a UTC day. Convert it to the reporting time zone before truncating.

SELECT
  date_trunc('day', created_at AT TIME ZONE 'America/Sao_Paulo') AS local_day,
  COUNT(*) AS orders
FROM orders
WHERE created_at >= now() - INTERVAL '30 days'
GROUP BY 1
ORDER BY 1;

Keep storage and index filters in timestamptz; use local time only for presentation or grouping. This avoids daylight-saving and server-time-zone surprises.

Choose the Right Bucket

Pair date_trunc with generate_series when empty buckets must appear. Pair it with EXPLAIN (ANALYZE, BUFFERS) when a report gets slow: an aggregate may be correct and still read far more data than it needs.