PostgreSQL date_trunc: buckets de tempo sem quebrar índices

date_trunc arredonda um timestamp para o início de uma unidade: hora, dia, semana, mês, trimestre ou ano. É ideal para agrupar eventos em buckets de relatório. A regra importante é onde você o usa: trunque o valor exibido ou agrupado, mas filtre uma coluna indexada com um intervalo simples de timestamps.

Crie um relatório diário

SELECT
  date_trunc('day', created_at) AS dia,
  COUNT(*) AS cadastros
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 significa “agrupe pela primeira expressão selecionada”. Isso mantém a query legível quando a expressão é longa.

A armadilha do índice

Esta query é conveniente, mas frequentemente impede o PostgreSQL de usar um índice B-tree normal em created_at:

-- Evite em tabelas grandes
SELECT COUNT(*)
FROM users
WHERE date_trunc('day', created_at) = DATE '2026-07-13';

Use um intervalo semiaberto. Ele inclui cada instante do dia sem depender de um limite artificial como 23:59:59.999.

-- Amigável a índice
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';

Em uma tabela grande, apoie isso com CREATE INDEX users_created_at_idx ON users (created_at) quando o filtro for frequente.

Semanas começam na segunda-feira

O PostgreSQL segue semanas ISO. date_trunc('week', ...) retorna segunda-feira à meia-noite, não domingo.

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

Se o negócio define semanas de outra forma, calcule explicitamente a fronteira em vez de assumir uma convenção regional.

Fuso horário: converta antes de agrupar

Um timestamptz é armazenado como instante absoluto. Um “dia” para um cliente em São Paulo não tem a mesma fronteira de um dia UTC. Converta para o fuso do relatório antes de truncar.

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

Mantenha armazenamento e filtros indexados em timestamptz; use a hora local somente para apresentação ou agrupamento. Isso evita surpresas com horário de verão e fuso do servidor.

Escolha o bucket certo

Combine date_trunc com generate_series quando buckets vazios precisam aparecer. Combine com EXPLAIN (ANALYZE, BUFFERS) quando um relatório ficar lento: uma agregação pode estar correta e ainda ler muito mais dados do que deveria.