Agendamentos recorrentes e fusos horários no PostgreSQL: padrões

Por que fusos horários e eventos recorrentes dão errado

A maioria dos bugs de calendário não são bugs de matemática. São bugs de significado. Você armazena uma coisa (um instante no tempo), mas os usuários esperam outra (uma hora no relógio local em um lugar específico). Essa lacuna é o motivo pelo qual agendamentos recorrentes e fusos horários podem parecer corretos em testes e então quebrar quando usuários reais aparecem.

O Horário de Verão (DST) é o gatilho clássico. Um deslocamento que é “todo domingo às 09:00” não é igual a “a cada 7 dias a partir de um timestamp inicial”. Quando o offset muda, essas duas ideias divergem por uma hora e seu calendário fica silenciosamente errado.

Viagens e fusos mistos acrescentam outra camada. Uma reserva pode estar vinculada a um local físico (uma cadeira de salão em Chicago), enquanto a pessoa que a visualiza está em Londres. Se você tratar um agendamento baseado no local como baseado na pessoa, mostrará a hora local errada para pelo menos um dos lados.

Modos comuns de falha:

• Você gera recorrências adicionando um intervalo a um timestamp armazenado e então o DST muda.
• Você armazena “horas locais” sem as regras de fuso, então não consegue reconstruir os instantes pretendidos depois.
• Você testa apenas datas que nunca cruzam uma fronteira de DST.
• Você mistura “fuso do evento”, “fuso do usuário” e “fuso do servidor” em uma consulta.

Antes de escolher um esquema, decida o que “correto” significa para seu produto.

Para uma reserva, “correto” geralmente significa: o compromisso ocorre na hora do relógio local pretendida no fuso do local, e qualquer pessoa que o visualize recebe uma conversão correta.

Para um turno, “correto” frequentemente significa: o turno começa em um horário local fixo para a loja, mesmo se um funcionário estiver viajando.

Essa decisão (agenda vinculada a um local vs. a uma pessoa) guia tudo: o que você armazena, como gera recorrências e como consulta uma visualização de calendário sem surpresas de uma hora.

Escolha o modelo mental certo: instante vs. hora local

Muitos bugs vêm de misturar duas ideias diferentes de tempo:

• Um instante: um momento absoluto que acontece uma vez.
• Uma regra de hora local: uma hora do relógio como “toda segunda às 9:00 em Paris”.

Um instante é o mesmo em todo lugar. “2026-03-10 14:00 UTC” é um instante. Chamadas de vídeo, partidas de voo e “envie esta notificação exatamente neste momento” geralmente são instantes.

Hora local é o que as pessoas leem no relógio em um lugar. “09:00 em Europe/Paris todo dia útil” é hora local. Horários de funcionamento, aulas recorrentes e turnos de equipe geralmente estão ancorados ao fuso horário de uma localização. O fuso é parte do significado, não uma preferência de exibição.

Uma regra prática simples:

• Armazene start/end como instantes quando o evento deve acontecer em um único momento real no mundo inteiro.
• Armazene data local e hora local mais um ID de fuso quando o evento deve seguir o relógio em um lugar.
• Se usuários viajam, mostre horários no fuso do visualizador, mas mantenha a agenda ancorada ao seu fuso.
• Não adivinhe um fuso a partir de offsets como "+02:00". Offsets não incluem regras de DST.

Exemplo: um turno hospitalar é “Seg-Sex 09:00-17:00 America/New_York.” Na semana da mudança de DST, o turno ainda é das 9 às 17 localmente, mesmo que os instantes UTC se movam uma hora.

Tipos do PostgreSQL que importam (e o que evitar)

A maioria dos bugs de calendário começa com um tipo de coluna errado. O ponto é separar um momento real da expectativa do relógio.

Use timestamptz para instantes reais: reservas, registros de ponto, notificações e qualquer coisa que você compare entre usuários ou regiões. O PostgreSQL armazena como um instante absoluto e converte para exibição, então ordenação e verificações de sobreposição se comportam conforme esperado.

Use timestamp without time zone para valores de relógio local que não são, por si só, instantes — como “toda segunda às 09:00” ou “loja abre às 10:00”. Combine com um identificador de fuso e converta para um instante real apenas ao gerar ocorrências.

Para padrões recorrentes, os tipos básicos ajudam:

• date para exceções somente por dia (feriados)
• time para uma hora de início diária
• interval para durações (como um turno de 6 horas)

Armazene o fuso como um nome IANA (por exemplo, America/New_York) em uma coluna text (ou em uma pequena tabela de lookup). Offsets como -0500 não bastam porque não carregam regras de horário de verão.

Um conjunto prático para muitos apps:

• timestamptz para start/end instantes de compromissos reservados
• date para dias de exceção
• time para hora local de início recorrente
• interval para duração
• text para o ID de fuso IANA

Opções de modelo de dados para apps de reservas e turnos

O melhor esquema depende de quão frequentemente as agendas mudam e de quão à frente as pessoas navegam. Você geralmente escolhe entre escrever muitas linhas antecipadamente ou gerá-las quando alguém abre um calendário.

Opção A: armazenar cada ocorrência

Insira uma linha por turno ou reserva (já expandida). É fácil consultar e fácil de raciocinar. A troca é gravações pesadas e muitas atualizações quando uma regra muda.

Funciona bem quando eventos são em sua maioria pontuais ou quando você apenas cria ocorrências para um curto prazo à frente (por exemplo, os próximos 30 dias).

Opção B: armazenar uma regra e expandir na leitura

Armazene uma regra de agenda (como “semanal às seg e qua às 09:00 em America/New_York”) e gere ocorrências para o intervalo solicitado sob demanda.

É flexível e econômico em armazenamento, mas as consultas ficam mais complexas. Visualizações mensais também podem ficar mais lentas, a menos que você faça cache dos resultados.

Opção C: regra mais ocorrências em cache (híbrido)

Mantenha a regra como fonte da verdade e também armazene ocorrências geradas para uma janela rolante (por exemplo, 60–90 dias). Quando a regra muda, regenere o cache.

Esse é um bom padrão padrão para apps de turnos: visualizações mensais permanecem rápidas, mas você ainda tem um único lugar para editar o padrão.

Um conjunto prático de tabelas:

• schedule: owner/resource, time zone, hora de início local, duração, regra de recorrência
• occurrence: instâncias expandidas com start_at timestamptz, end_at timestamptz, além do status
• exception: marcadores “pular esta data” ou “esta data é diferente”
• override: edições por ocorrência, como horário alterado, troca de funcionário, flag de cancelado
• (opcional) schedule_cache_state: última faixa gerada para saber o que preencher a seguir

Para consultas por intervalo de calendário, indexe para “mostre tudo nesta janela”:

• Em occurrence: btree (resource_id, start_at) e frequentemente btree (resource_id, end_at)
• Se você consulta “sobrepõe intervalo” com frequência: um tstzrange(start_at, end_at) gerado mais um índice gist

Representando regras de recorrência sem torná-las frágeis

Agendas recorrentes quebram quando a regra é esperta demais, flexível demais ou armazenada como um blob não consultável. Um bom formato de regra é aquele que sua aplicação pode validar e que sua equipe consegue explicar rapidamente.

Duas abordagens comuns:

• Campos custom simples para os padrões que você realmente oferece (turnos semanais, datas de cobrança mensais).
• Regras estilo iCalendar (RRULE) quando você precisa importar/exportar calendários ou suportar muitas combinações.

Um meio-termo prático: permita um conjunto limitado de opções, armazene-as em colunas e trate qualquer string RRULE como intercâmbio apenas.

Por exemplo, uma regra semanal pode ser expressa com campos como:

• freq (daily/weekly/monthly) e interval (a cada N)
• byweekday (um array de 0-6 ou uma máscara de bits)
• bymonthday opcional (1-31) para regras mensais
• starts_at_local (a data+hora local que o usuário escolheu) e tzid
• until_date ou count opcional (evite suportar ambos a menos que realmente precise)

Para limites, prefira armazenar duração (por exemplo, 8 horas) em vez de armazenar um timestamp de fim para cada ocorrência. Duração permanece estável quando os relógios mudam. Você ainda pode calcular o fim por ocorrência como: início da ocorrência + duração.

Ao expandir uma regra, mantenha-a segura e limitada:

• Expanda apenas dentro de window_start e window_end.
• Adicione um pequeno buffer (por exemplo, 1 dia) para eventos que atravessam a madrugada.
• Pare após um número máximo de instâncias (como 500).
• Filtre candidatos primeiro (por tzid, freq e data de início) antes de gerar.

Passo a passo: construir uma agenda recorrente segura para DST

Um padrão confiável é: trate cada ocorrência como uma ideia de calendário local primeiro (data + hora local + fuso do local), e então converta para um instante somente quando precisar ordenar, checar conflitos ou exibir.

1) Armazene a intenção local, não suposições em UTC

Salve o fuso da localização da agenda (nome IANA como America/New_York) mais uma hora de início local (por exemplo 09:00). Essa hora local é o que o negócio quer, mesmo quando o DST muda.

Armazene também uma duração e limites claros para a regra: uma data de início e ou uma data de término ou um número de repetições. Limites impedem bugs de “expansão infinita”.

2) Modele exceções e overrides separadamente

Use duas tabelas pequenas: uma para datas puladas e outra para ocorrências alteradas. Chaveie por schedule_id + local_date para que você consiga casar com a recorrência original de maneira limpa.

Uma forma prática fica assim:

-- core schedule
-- tz is the location time zone
-- start_time is local wall-clock time
schedule(id, tz text, start_date date, end_date date, start_time time, duration_mins int, by_dow int[])

schedule_skip(schedule_id, local_date date)

schedule_override(schedule_id, local_date date, new_start_time time, new_duration_mins int)

3) Expanda apenas dentro da janela solicitada

Gere datas locais candidatas para o intervalo que você está renderizando (semana, mês). Filtre por dia da semana, então aplique skips e overrides.

WITH days AS (
  SELECT d::date AS local_date
  FROM generate_series($1::date, $2::date, interval '1 day') d
), base AS (
  SELECT s.id, s.tz, days.local_date,
         make_timestamp(extract(year from days.local_date)::int,
                        extract(month from days.local_date)::int,
                        extract(day from days.local_date)::int,
                        extract(hour from s.start_time)::int,
                        extract(minute from s.start_time)::int, 0) AS local_start
  FROM schedule s
  JOIN days ON days.local_date BETWEEN s.start_date AND s.end_date
  WHERE extract(dow from days.local_date)::int = ANY (s.by_dow)
)
SELECT b.id,
       (b.local_start AT TIME ZONE b.tz) AS start_utc
FROM base b
LEFT JOIN schedule_skip sk
  ON sk.schedule_id = b.id AND sk.local_date = b.local_date
WHERE sk.schedule_id IS NULL;

4) Converta para o fuso do visualizador por último

Mantenha start_utc como timestamptz para ordenação, checagens de conflito e reservas. Só ao exibir, converta para o fuso do visualizador. Isso evita surpresas de DST e mantém as visualizações consistentes.

Padrões de consulta para gerar uma visualização de calendário correta

Uma tela de calendário é geralmente uma consulta por intervalo: “mostre tudo entre from_ts e to_ts.” Um padrão seguro é:

1. Expanda apenas candidatos dentro dessa janela.
2. Aplique exceções/overrides.
3. Retorne linhas finais com start_at e end_at como timestamptz.

Expansão diária ou semanal com generate_series

Para regras semanais simples (como “todo Seg-Sex às 09:00 local”), gere datas locais no fuso da agenda e então transforme cada data local + hora local em um instante.

-- Inputs: :from_ts, :to_ts are timestamptz
-- rule.tz is an IANA zone like 'America/New_York'
WITH bounds AS (
  SELECT
    (:from_ts AT TIME ZONE rule.tz)::date AS from_local_date,
    (:to_ts   AT TIME ZONE rule.tz)::date AS to_local_date
  FROM rule
  WHERE rule.id = :rule_id
), days AS (
  SELECT d::date AS local_date
  FROM bounds, generate_series(from_local_date, to_local_date, interval '1 day') AS g(d)
)
SELECT
  (local_date + rule.start_local_time) AT TIME ZONE rule.tz AS start_at,
  (local_date + rule.end_local_time)   AT TIME ZONE rule.tz AS end_at
FROM rule
JOIN days ON true
WHERE EXTRACT(ISODOW FROM local_date) = ANY(rule.by_isodow);

Isso funciona bem porque a conversão para timestamptz acontece por ocorrência, então as mudanças de DST são aplicadas no dia correto.

Regras mais complexas com um CTE recursivo

Quando regras dependem de “n‑ésimo dia da semana”, lacunas ou intervalos customizados, um CTE recursivo pode gerar a próxima ocorrência repetidamente até ultrapassar to_ts. Mantenha a recursão ancorada à janela para que ela não rode indefinidamente.

Depois de ter linhas candidatas, aplique overrides e cancelamentos juntando as tabelas de exceção em (rule_id, start_at) ou em uma chave local como (rule_id, local_date). Se houver um registro de cancelamento, descarte a linha. Se houver um override, substitua start_at/end_at pelos valores do override.

Padrões de desempenho que importam mais:

• Constrinja o intervalo cedo: filtre regras primeiro, então expanda só dentro de [from_ts, to_ts).
• Indexe tabelas de exceção/override em (rule_id, start_at) ou (rule_id, local_date).
• Evite expandir anos de dados para uma visualização de mês.
• Faça cache de ocorrências expandidas somente se você conseguir invalidá-las limpamente quando regras mudarem.

Lidando com exceções e overrides de forma limpa

Agendas recorrentes só são úteis se você puder quebrá-las com segurança. Em apps de reservas e turnos, a “semana normal” é a regra base, e todo o resto é uma exceção: feriados, cancelamentos, compromissos movidos ou trocas de pessoal. Se exceções forem adicionadas depois, as visualizações de calendário divergem e duplicatas aparecem.

Mantenha três conceitos separados:

• Uma agenda base (a regra recorrente e seu fuso)
• Skips (datas ou instâncias que não devem ocorrer)
• Overrides (uma ocorrência que existe, mas com detalhes alterados)

Use uma ordem de precedência fixa

Escolha uma ordem e mantenha-a consistente. Uma escolha comum:

1. Gere candidatos a partir da recorrência base.
2. Aplique overrides (substitua a gerada).
3. Aplique skips (oculte-a).

Garanta que a regra seja fácil de explicar ao usuário em uma frase.

Evite duplicatas quando um override substitui uma instância

Duplicatas ocorrem quando uma consulta retorna tanto a ocorrência gerada quanto a linha de override. Previna isso com uma chave estável:

• Dê a cada instância gerada uma chave estável, como (schedule_id, local_date, start_time, tzid).
• Armazene essa chave na linha de override como a “chave da ocorrência original”.
• Adicione uma constraint única para que exista apenas um override por ocorrência base.

Então, nas consultas, exclua ocorrências geradas que têm um override correspondente e una as linhas de override.

Mantenha auditabilidade sem atrito

Exceções são onde disputas acontecem (“Quem mudou meu turno?”). Adicione campos básicos de auditoria em skips e overrides: created_by, created_at, updated_by, updated_at e um motivo opcional.

Erros comuns que causam bugs de menos/mais uma hora

A maioria dos bugs de uma hora vem de confundir dois significados de tempo: um instante (um ponto na linha do tempo UTC) e uma leitura do relógio local (como 09:00 todo segunda em New York).

Um erro clássico é armazenar uma regra local de relógio como timestamptz. Se você salvar “Segundas às 09:00 America/New_York” como um único timestamptz, você já escolheu uma data específica (e o estado de DST). Mais tarde, ao gerar segundas futuras, a intenção original (“sempre 09:00 local”) se perde.

Outra causa frequente é confiar em offsets UTC fixos como -05:00 em vez de um nome de zona IANA. Offsets não incluem regras de DST. Armazene o ID da zona (por exemplo, America/New_York) e deixe o PostgreSQL aplicar as regras corretas para cada data.

Cuidado com quando você converte. Se você converter para UTC cedo demais ao gerar uma recorrência, pode congelar um offset de DST e aplicá‑lo a todas as ocorrências. Um padrão mais seguro é: gere ocorrências em termos locais (data + hora local + zona) e então converta cada ocorrência para um instante.

Erros que aparecem repetidamente:

• Usar timestamptz para armazenar uma hora do dia recorrente (você precisava de time + tzid + uma regra).
• Armazenar apenas um offset, não o nome da zona IANA.
• Converter durante a geração de recorrência em vez de no final.
• Expandir recorrências “para sempre” sem uma janela de tempo rígida.
• Não testar a semana de início do DST e a semana de fim do DST.

Um teste simples que pega a maioria dos problemas: escolha uma zona com DST, crie um turno semanal às 09:00 e renderize um calendário de dois meses que cruza uma mudança de DST. Verifique que cada instância aparece como 09:00 local, mesmo que os instantes UTC subjacentes sejam diferentes.

Checklist rápido antes de lançar

Antes de liberar, verifique o básico:

• Cada agenda está vinculada a um local (ou unidade de negócio) com um fuso nomeado, armazenado na própria agenda.
• Você armazena IDs de zona IANA (como America/New_York), não offsets crus.
• A expansão de recorrência gera ocorrências apenas dentro da faixa solicitada.
• Exceções e overrides têm uma ordem de precedência única e documentada.
• Você testa semanas de mudança de DST e um visualizador em um fuso diferente do da agenda.

Faça um ensaio realista: uma loja em Europe/Berlin tem um turno semanal às 09:00 hora local. Um gerente visualiza a partir de America/Los_Angeles. Confirme que o turno permanece 09:00 em Berlim toda semana, mesmo quando cada região cruza o DST em datas diferentes.

Exemplo: turnos semanais com um feriado e mudança de DST

Uma pequena clínica tem um turno recorrente: toda segunda, 09:00–17:00 no fuso local da clínica (America/New_York). A clínica fecha por um feriado em uma segunda específica. Um funcionário está viajando pela Europa por duas semanas, mas a agenda da clínica precisa permanecer ligada ao relógio da clínica, não à localização atual do funcionário.

Para que isso se comporte corretamente:

• Armazene uma regra de recorrência ancorada em datas locais (weekday = Monday, horas locais = 09:00–17:00).
• Armazene o fuso da agenda (America/New_York).
• Armazene uma data de início efetiva para que a regra tenha uma âncora clara.
• Armazene uma exceção para cancelar a segunda-feira do feriado (e overrides para mudanças pontuais).

Agora renderize um intervalo de calendário de duas semanas que inclua uma mudança de DST em New York. A consulta gera segundas nessa faixa de datas locais, anexa as horas locais da clínica e então converte cada ocorrência em um instante absoluto (timestamptz). Como a conversão acontece por ocorrência, o DST é tratado no dia certo.

Visualizadores diferentes vêem horários locais diferentes para o mesmo instante:

• Um gerente em Los Angeles o vê mais cedo no relógio.
• Um funcionário em viagem em Berlin o vê mais tarde no relógio.

A clínica ainda recebe o que queria: 09:00–17:00 horário de New York, toda segunda que não for cancelada.

Próximos passos: implementar, testar e manter legível

Defina sua abordagem de tempo cedo: você vai armazenar apenas regras, apenas ocorrências ou um híbrido? Para muitos produtos de reservas e turnos, um híbrido funciona bem: mantenha a regra como fonte da verdade, armazene um cache rolante se necessário e armazene exceções e overrides como linhas concretas.

Registre seu “contrato de tempo” em um lugar: o que conta como um instante, o que conta como hora local e quais colunas armazenam cada um. Isso evita divergência em que um endpoint retorna hora local enquanto outro retorna UTC.

Mantenha a geração de recorrência como um módulo único, não como fragmentos SQL espalhados. Se algum dia você mudar como interpreta “09:00 AM hora local”, você quer um lugar só para atualizar.

Se você estiver construindo uma ferramenta de agendamento sem codificar tudo à mão, AppMaster (appmaster.io) é uma opção prática para esse tipo de trabalho: você pode modelar o banco no Data Designer, montar a lógica de recorrência e exceção em processos de negócio visuais e ainda gerar backend e código de app reais.