DATE_BIN no PostgreSQL: baldes de tempo de largura arbitraria para metricas

O DATE_BIN chegou no PostgreSQL 14 e resolve o que o DATE_TRUNC nao consegue: ele encaixa um timestamp no inicio de um balde de qualquer largura, 15 minutos, 6 horas, 10 dias, medido a partir de uma origem escolhida. E a ferramenta certa para metricas com um passo nao padronizado.

Sintaxe e intuicao

A assinatura e simples: DATE_BIN(stride, source, origin). O primeiro argumento e um interval (a largura do balde), o segundo e o timestamp que voce quer agrupar, e o terceiro e a origin, o ponto a partir do qual os intervalos sao contados.

SELECT DATE_BIN(
  INTERVAL '15 minutes',
  TIMESTAMP '2024-01-01 14:37:09',
  TIMESTAMP '2024-01-01'
);
-- 2024-01-01 14:30:00

O modelo mental: imagine uma regua infinita de baldes com largura stride comecando em origin. O DATE_BIN encontra o balde em que source cai e devolve a sua borda esquerda.

• Sempre trunca para baixo, igual ao DATE_TRUNC.
• O resultado se encaixa na grade de origin, nao no inicio de uma hora ou de um dia.
• O stride deve ser um numero inteiro de unidades de tempo fixas (segundos, minutos, horas, dias, semanas).

Onde o DATE_TRUNC nao alcanca

O DATE_TRUNC so conhece um conjunto fixo de campos: hora, dia, mes. Ele nao consegue construir um balde de 15 minutos ou de 6 horas. Com o DATE_BIN isso e uma unica linha:

SELECT
  DATE_BIN(INTERVAL '15 minutes', created_at, TIMESTAMP '2024-01-01') AS bucket,
  COUNT(*)    AS orders,
  SUM(amount) AS revenue
FROM orders
WHERE status = 'paid'
GROUP BY bucket
ORDER BY bucket;

Contar atividade em janelas de 6 horas e igualmente facil, os cadastros por exemplo:

SELECT
  DATE_BIN(INTERVAL '6 hours', created_at, TIMESTAMP '2024-01-01') AS shift,
  COUNT(*) AS signups
FROM users
GROUP BY shift
ORDER BY shift;

Mude apenas o stride e voce obtem qualquer passo, sem subconsultas e sem aritmetica com EXTRACT.

Por que a origin importa

A origin define a fase da grade. Para 15 minutos ou uma hora quase nunca importa, mas assim que o stride nao divide o dia de forma exata, a origem se torna critica.

SELECT DATE_BIN(
  INTERVAL '7 days',
  TIMESTAMP '2024-03-20 10:00:00',
  TIMESTAMP '2024-01-01'
);
-- 2024-03-18 00:00:00

Aqui as semanas sao cortadas a partir de 1 de janeiro, nao a partir da segunda-feira ISO. Desloque a origin para o dia da semana que voce quiser e tera "semanas" que comecam exatamente quando o negocio precisa.

• Use a mesma origin em todas as consultas para que os baldes coincidam entre relatorios.
• Para um dia deslocado (um expediente que comeca as 9 da manha) defina uma origin como TIMESTAMP '2024-01-01 09:00:00'.

Pegadinhas: tipos e fusos horarios

A armadilha principal e a incompatibilidade de tipos. Se source for timestamptz, entao origin tambem precisa ser timestamptz, ou voce recebe um erro. Alem disso, para timestamptz os bins sao calculados em UTC, entao seus baldes de "6 horas" se deslocam em relacao a hora local.

SELECT
  DATE_BIN(
    INTERVAL '6 hours',
    created_at AT TIME ZONE 'America/Sao_Paulo',
    TIMESTAMP '2024-01-01'
  ) AS shift_br,
  COUNT(*)
FROM orders
GROUP BY shift_br;

Mais alguns ancinhos para pisar:

• Meses e anos sao proibidos: INTERVAL '1 month' gera um erro porque sua duracao nao e constante. Use o DATE_TRUNC para meses.
• O DATE_BIN so existe a partir do PostgreSQL 14, ele esta ausente na 13 e em versoes anteriores.

Alternativas: TimescaleDB e ClickHouse

O DATE_BIN e solido, mas o ecossistema oferece opcoes mais flexiveis.

• TimescaleDB traz o time_bucket, o ancestral do DATE_BIN. Ele tem semantica compativel, mas recursos mais ricos: intervalos de um mes, um argumento offset e fusos horarios passados diretamente.

SELECT
  time_bucket(INTERVAL '15 minutes', created_at) AS bucket,
  SUM(amount) AS revenue
FROM orders
GROUP BY bucket
ORDER BY bucket;

• ClickHouse resolve o mesmo problema com toStartOfInterval:

SELECT
  toStartOfInterval(created_at, INTERVAL 15 MINUTE) AS bucket,
  sum(amount) AS revenue
FROM orders
GROUP BY bucket
ORDER BY bucket;

A conclusao: use o DATE_TRUNC para unidades padrao, o DATE_BIN para um passo arbitrario no PostgreSQL puro, e o time_bucket quando precisar de meses, deslocamentos e fusos prontos.