Fusos horários e marcas de data e hora

Os fusos horários são um ponto em que candidatos confiantes tropeçam, por isso os entrevistadores os exploram para avaliar a profundidade do conhecimento. A pergunta central é sempre: “como você armazena e compara carimbos de data e hora entre regiões?”

A resposta profissional é uma prática disciplinada, não uma função: armazene tudo em UTC e converta apenas nas extremidades, para exibição. Com o modelo de armazenamento correto, a maioria das consultas se torna trivial.

• carimbo de data e hora vs carimbo de data e hora com fuso horário
• Conversão entre fusos
• UTC como fonte de verdade

O PostgreSQL tem dois tipos de carimbo de data e hora, e confundi-los é um erro comum em entrevistas.

• timestamp (sem fuso horário): um valor do relógio local sem fuso associado. Ele armazena exatamente o valor fornecido.
• timestamptz (com fuso horário): armazenado internamente como UTC; na entrada, é convertido a partir do fuso da sessão e, na saída, é convertido novamente.

Apesar do nome, timestamptz não armazena um fuso; ele armazena um instante preciso em UTC. Esse detalhe impressiona os entrevistadores.

A regra de ouro. Armazene os instantes em UTC (use timestamptz) e converta para um fuso local somente ao apresentá-los a um usuário. Isso evita ambiguidades relacionadas ao horário de verão e faz com que a ordenação por horário esteja correta em todos os lugares.

Se perguntarem “por que UTC?”, responda: UTC não tem mudanças para o horário de verão, portanto o mesmo horário local nunca se repete nem é pulado, ao contrário do horário local.

AT TIME ZONE é uma construção inteligente e uma armadilha frequente, porque faz duas coisas opostas dependendo do tipo de entrada:

• Quando aplicado a um timestamptz, converte o instante absoluto para esse fuso e retorna um timestamp simples (o horário local exibido pelo relógio).
• Quando aplicado a um timestamp simples, ele interpreta esse horário local como pertencente a esse fuso e retorna um timestamptz.

Entender em que direção ele funciona é todo o segredo.

Conheça suas funções de "agora". NOW() e CURRENT_TIMESTAMP retornam um timestamptz no Postgres. Elas retornam o horário do início da transação, não da instrução, o que é importante em transações longas.

Para obter UTC explicitamente, faça a conversão: NOW() AT TIME ZONE 'UTC'. No MySQL, UTC_TIMESTAMP() fornece UTC diretamente.

Entrevistadores adoram casos extremos de DST. Quando os relógios avançam, uma hora do relógio local não existe; quando retrocedem, uma hora se repete. Armazenar a hora local torna esses casos ambíguos ou inválidos.

Armazenar UTC evita completamente esse problema: cada instante é único e monotônico. Especificar uma região como 'America/New_York' (e não um deslocamento fixo como -05:00) permite que o banco de dados aplique corretamente as regras de DST para qualquer data.

Um problema realista: "usuários ativos diariamente no horário local de cada usuário". Se você truncar diretamente o carimbo de data e hora em UTC, os limites da meia-noite ficarão errados para usuários fora de UTC.

Converta para o fuso do usuário antes de truncar para o dia. A conversão desloca o horário local para que os limites do dia fiquem alinhados localmente.

Ao filtrar uma coluna timestamptz, compare-a com um instante explícito, de preferência um literal em UTC ou um timestamptz com deslocamento. Comparar com uma string isolada pode fazer com que ela seja interpretada no fuso imprevisível da sessão.

Assim, a comparação permanece sem ambiguidades, independentemente de quem execute a consulta.

Muitos sistemas armazenam o horário como uma época Unix (segundos desde 1970-01-01 UTC). Entrevistadores podem fornecer uma coluna de inteiros e pedir que você a interprete.

• Postgres: TO_TIMESTAMP(epoch_seconds) retorna um timestamptz.
• De volta à época: EXTRACT(EPOCH FROM occurred_at).
• MySQL: FROM_UNIXTIME() e UNIX_TIMESTAMP().

Os valores de época são inerentemente UTC, o que explica em parte sua popularidade para armazenamento.

Um resumo rápido para que você demonstre domínio em qualquer ambiente:

• Postgres: timestamptz + AT TIME ZONE, com o suporte mais completo.
• MySQL: TIMESTAMP converte automaticamente por meio de time_zone da sessão; CONVERT_TZ(t, from, to) faz a conversão explicitamente. DATETIME não reconhece fusos horários.
• SQL Server: datetimeoffset armazena um deslocamento; AT TIME ZONE 'name' faz a conversão usando os nomes de fusos do Windows.

Uma questão sutil de relatórios: contabilizar sessões por dia do calendário local quando uma sessão pode atravessar a meia-noite. A solução segue a mesma disciplina: converta para a hora local e depois agrupe.

Armazene o início e o fim como timestamptz; nos relatórios, derive o dia local a partir do início convertido. Se uma sessão precisar ser dividida entre dois dias, você poderá fazer uma junção com uma sequência de dias, um ótimo ponto para mencionar como pergunta complementar.

A disciplina que você deve levar consigo:

• Armazene UTC como timestamptz; converta para um fuso identificado por nome somente para exibição.
• timestamptz armazena um instante UTC, não um fuso, apesar do nome.
• AT TIME ZONE funciona em ambas as direções, dependendo do tipo de entrada: converte um timestamptz para o horário local ou interpreta um timestamp simples como pertencente a um fuso.
• Use nomes de regiões ('America/New_York') para que o DST seja aplicado automaticamente; evite deslocamentos fixos.
• Converta para a hora local antes de truncar para um dia e compare as colunas com instantes UTC explícitos.