Como agrupar por semana no PostgreSQL

Precisa analisar dados por semana no PostgreSQL? Veja como agrupar seus registros usando funções de data incorporadas para obter insights claros e baseados em tempo.

O agrupamento de dados por semana é um requisito comum em relatórios. Ele é frequentemente usado em relatórios comerciais para rastrear métricas como receita semanal, inscrições de usuários ou tráfego do site. O agrupamento por semana ajuda a suavizar as flutuações diárias e oferece uma visão mais clara das tendências ao longo do tempo. É especialmente útil ao comparar o desempenho em intervalos de tempo consistentes, como o crescimento "Semana a Semana" ou a análise de coorte semanal.

O PostgreSQL oferece ferramentas poderosas para trabalhar com datas, mas o agrupamento por semana - especialmente quando você precisa de um dia de início ou fuso horário específico - pode ser complicado se você não estiver familiarizado com as funções certas.

Este artigo o orienta sobre como agrupar por semana no PostgreSQL usando date_trunc() e date_bin(), como ajustar para fusos horários e como definir um dia de início de semana personalizado, como quarta ou quinta-feira.

Deseja desenvolver habilidades práticas e sólidas no PostgreSQL desde o início? Experimente o cursoSQL de A a Z no PostgreSQL em LearnSQL.com.br. Ele foi projetado para levá-lo do iniciante ao usuário SQL confiante com exercícios do mundo real e orientação passo a passo.

Agrupamento por semana com date_trunc()

A função date_trunc() é a maneira mais comum de agrupar dados por semana no PostgreSQL. Ela trunca um carimbo de data/hora ou data para o início de uma unidade de tempo especificada, como dia, semana ou mês.

Uso básico:

date_trunc('week', some_date)

Isso retornará uma nova data que representa o início da semana ISO, que é segunda-feira às 00:00:00.

Exemplo:

Digamos que você tenha uma coluna created_at e queira contar as inscrições por semana:

SELECT
  date_trunc('week', created_at) AS week_start,
  COUNT(*) AS signups
FROM users
GROUP BY week_start
ORDER BY week_start;

Essa consulta agrupa os usuários pela semana em que se inscreveram, com cada semana começando na segunda-feira.

Veja como pode ser o resultado dessa consulta:

Observe que o resultado de date_trunc('week', some_date) inclui a data e a hora, com a hora definida como 00:00:00 (meia-noite).

Agrupamento por semana em um fuso horário específico

Ao trabalhar com carimbos de data/hora, especialmente em aplicativos globais, o conhecimento do fuso horário é essencial. A atividade de um usuário pode ocorrer no domingo à noite em um fuso horário, mas aparecer como segunda-feira de manhã no UTC. Isso pode afetar a forma como os dados são agrupados em semanas - levando a relatórios incorretos se os fusos horários forem ignorados.

Por padrão, o PostgreSQL agrupa os carimbos de data/hora com base no fuso horário da sessão, que geralmente é definido quando a conexão é estabelecida. Isso significa que o date_trunc('week', some_timestamptz_column) usará a configuração de fuso horário atual da sessão. Se você não estiver definindo explicitamente o fuso horário, o PostgreSQL usará o padrão da configuração do seu servidor ou cliente.

Para garantir a consistência - especialmente em relatórios - você pode usar a cláusula AT TIME ZONE para converter os registros de data e hora em um fuso horário específico antes de truncá-los.

Exemplo de uso:

SELECT
  date_trunc('week', created_at AT TIME ZONE 'UTC') AS week_start_utc,
  COUNT(*) AS signups
FROM users
GROUP BY week_start_utc
ORDER BY week_start_utc;

Essa consulta trunca cada carimbo de data/hora created_at para o início da semana em UTC, garantindo que os agrupamentos semanais sejam consistentes em todos os fusos horários.

Se estiver trabalhando com uma coluna timestamptz e quiser agrupar por uma hora local específica, use:

date_trunc('week', created_at AT TIME ZONE 'America/New_York')

Isso alinha suas semanas com o início da segunda-feira no horário local de Nova York.

Dias de início de semana personalizados (por exemplo, quarta ou quinta-feira)

A função date_trunc('week', some_date) no PostgreSQL sempre alinha as semanas para que comecem na segunda-feira, seguindo a definição de semana ISO. Isso é fixo e não pode ser alterado diretamente em date_trunc().

Se o seu relatório exigir que as semanas comecem em um dia diferente - como quarta ou quinta-feira -, você precisará aplicar uma solução simples: altere a data antes do truncamento e, depois, altere-a novamente.

Lógica da solução alternativa

Para fazer com que a semana comece em um dia específico, subtraia o número apropriado de dias antes do truncamento e, em seguida, adicione-o novamente:

-- Week starting on Wednesday
date_trunc('week', some_date - interval '2 days') + interval '2 days'

Isso atrasa o calendário em 2 dias (de modo que segunda-feira se torna sábado), trunca para o início dessa semana ajustada (sábado) e, em seguida, avança novamente para quarta-feira.

Exemplos de diferentes dias de início de semana

Use esse padrão dentro das cláusulas SELECT, GROUP BY ou ORDER BY para alinhar os dados à estrutura semanal de sua preferência.

Agrupamento flexível de data e hora com date_bin() (PostgreSQL 14+)

O PostgreSQL 14 introduziu uma nova e poderosa função: date_bin(). Ela lhe dá mais controle do que date_trunc() ao agrupar por hora, especialmente se você quiser que as semanas comecem em uma data específica ou em um dia da semana personalizado.

date_bin() permite agrupar carimbos de data/hora em períodos de tempo iguais - como semanas, dias ou horas - a partir de qualquer data e hora que você escolher. Esse ponto de partida é chamado de âncora.

A âncora define quando o primeiro grupo começa. O PostgreSQL, então, constrói os outros grupos com base nisso - tanto para frente quanto para trás no tempo. Portanto, se sua âncora for 1º de janeiro de 2025 e você agrupar por 7 dias, o PostgreSQL criará intervalos de uma semana a partir dessa data e também intervalos anteriores que se encaixam no mesmo padrão.

Sintaxe

date_bin(interval, timestamp, anchor)

• interval: qual deve ser a duração de cada grupo (por exemplo, '7 days')
• timestamp: a coluna que você está agrupando (por exemplo, created_at)
• anchor: a data e a hora exatas em que o agrupamento deve se alinhar

Exemplo: Agrupamento por intervalos de uma semana a partir de 1º de janeiro

SELECT
  date_bin('7 days', created_at, TIMESTAMP '2025-01-01') AS custom_week,
  COUNT(*) AS signups
FROM users
GROUP BY custom_week
ORDER BY custom_week;

Isso agrupa os usuários em intervalos de 7 dias a partir de 1º de janeiro de 2025, independentemente do dia da semana. Você pode usar qualquer data âncora que desejar - por exemplo, o início do seu ano fiscal ou o dia do lançamento do seu produto.

Use date_bin() se quiser que as semanas comecem em um dia personalizado ou em uma data exata, se precisar de intervalos de tempo que não sejam baseados em unidades de calendário (por exemplo, a cada 10 dias ou 6 horas) ou se quiser ter controle total sobre como os dados baseados em tempo são agrupados.

Se estiver usando o PostgreSQL 14 ou mais recente, o date_bin() é uma alternativa flexível que pode tornar seus relatórios semanais mais precisos e alinhados com suas necessidades específicas.

Conclusão

O agrupamento por semana no PostgreSQL pode ser simples ou altamente personalizável, dependendo de suas necessidades. Você pode usar date_trunc() para semanas ISO padrão (começando na segunda-feira) ou aplicar pequenos ajustes para iniciar as semanas em qualquer dia que desejar. Para obter ainda mais flexibilidade - como definir uma data âncora específica ou usar intervalos de tempo fora do padrão -date_bin() (disponível no PostgreSQL 14 e superior) é a melhor ferramenta para o trabalho.

Entender como trabalhar com datas, fusos horários e lógica de agrupamento é essencial para qualquer pessoa que esteja fazendo análises de dados ou relatórios sérios no PostgreSQL.

Se você estiver pronto para aprimorar suas habilidades no PostgreSQL com a prática do mundo real, confira o curso SQL de A a Z no PostgreSQL em LearnSQL.com.br. É um caminho de aprendizado prático, passo a passo, que o ajudará a ir do básico a técnicas mais avançadas, como as abordadas neste artigo.