Como o PostgreSQL trata as time zones, horário de verão e conversões de string para timestamp?

O PostgreSQL tem uma maneira muito estranha de lidar com timestamps e fusos horários, mas consegui decifrar a lógica por trás. Tenho certeza que depois de entender como funciona, poderá fazer muito mais coisas e análises com o PostgreSQL sem preocupações com horários.

A primeira regra é: sempre use as colunas do tipo TIMESTAMPTZ (também chamadas de TIMESTAMP WITH TIME ZONE). Essas colunas salvam sempre o valor em formato UTC, e as exibem (no pgAdmin, por exemplo) usando o fuso horário do banco.

O PostgreSQL guarda em um lugar um fuso horário (time zone) padrão para todas as suas consultas. Para descobrir esse esse fuso horário, execute:

SHOW timezone;

Alterando o fuso horário do PostgreSQL

Como alterar para sempre o fuso horário de um banco de dados?

ALTER DATABASE nome_do_banco SET timezone = 'America/Sao_Paulo';

Como alterar o horário em uma conexão JDBC?

O Java e a biblioteca JDBC possuem um comportamento próprio ao lidar com fusos horários. Por mais que o banco inteiro esteja definido para UTC, o driver JDBC forçará todas suas conexões a abrirem com o fuso horário definido pela JVM. Veja mais sobre isso nessa discussão do StackOverflow.

Ao enviar a query:

SELECT '2010-01-01T01:01'::TIMESTAMPTZ;

O timestamp será salvo como se estivesse no fuso horário da máquina.

Conheço 5 soluções:

1) Se você está com problemas de inconsistência de datas e está criando TIMESTAMPs dessa forma usando strings, tipo:

SELECT '2010-01-01T01:01'::TIMESTAMPTZ;

Você pode inserir strings em UTC colocando um Z no final:

SELECT '2010-01-01T01:01Z'::TIMESTAMPTZ;

2) Se você está usando o HikariCP para gerenciar as conexões do PostgreSQL no JDBC, corrija o timezone da seguinte forma:

hikariConf.connectionInitSql = "SET TIME ZONE 'UTC'"

3) Alterar o fuso horário do seu sistema operacional.

4) Alterar o fuso horário mudando os parâmetros de inicialização da JVM.

AddVMOption -Duser.timezone=UTC

5) Alterar o fuso horário da JVM dentro do código

TimeZone.setDefault(TimeZone.getTimeZone("UTC"));

Como alterar o fuso horário apenas dentro de uma sessão?

Uma sessão é criada quando você abre uma conexão com o banco de dados. Tudo o que for modificado no escopo da sessão será perdido quando a conexão for terminada.

SET TIME ZONE 'Continente/Cidade';# OUSET TIMEZONE TO 'Continente/Cidade';

Como alterar o fuso horário apenas dentro de uma transação?

BEGIN;SET LOCAL TIME ZONE 'Continente/Cidade';...#SET LOCAL TIMEZONE TO 'Continente/Cidade';COMMIT;

Como alterar o fuso horário para o servidor inteiro e também os fusos dos logs?

Localize e abra para edição o arquivo postgresql.conf
No Windows:

C:\Program Files\PostgreSQL\(versão do seu PostgreSQL)\data

No Ubuntu

/etc/postgresql/(versão do seu PostgreSQL)/main/postgresql.conf

Depois de abrir, procure por timezone, pode ser que você encontre estas linhas:

timezone = 'Continente/Cidade'log_timezone = 'Continente/Cidade'

Pode ser que estas linhas comecem com #, isso significa que aquela linha tão tem um fuso horário padrão definido, então o fuso GMT (equivalente ao UTC) será usado. Há pessoas que recomendam não definir nenhum fuso, para isso basta colocar # no começo das linhas:

#timezone = 'Continente/Cidade'#log_timezone = 'Continente/Cidade'

...mas isso fica à seu critério.

Confirmando o funcionamento de horários de verão no PostgreSQL

Ao rodar a query abaixo você pode verificar como o PostgreSQL lida com os fusos horários. Perceba que o dia 04/11/2018 tem apenas 23h:

BEGIN;SET LOCAL TIME ZONE 'America/Sao_Paulo';SELECT '2018-11-04T00:00'::TIMESTAMPTZ - '2018-11-05T00:00'::TIMESTAMPTZ AS duracao_do_dia;COMMIT;

Isso significa que o primeiro dia do início do horário de verão começa às 01:00 e não às 00:00.

Como o PostgreSQL divide os fusos horários?

O PostgreSQL usa um banco de dados à parte chamado tz database ou olson database (em homenagem ao autor do banco que até hoje o atualiza).

Para descobrir os fusos horários cadastrados no PostgreSQL, execute a query:

SELECT * FROM pg_timezone_names

Mas o que essas colunas significam?

• name: o nome único do fuso horário
• abbrev: a abreviação daquele fuso horário (nunca use isso!) (essa abreviação não é única para cada "name"! Ao usá-la você pode confundir fusos, especialmente nos fusos que têm horário de verão)
• utc_offset: quanto tempo este fuso horário têm de diferença (em INSTANT) do fuso horário UTC, no exato momento da consulta.
• is_dst: se no no exato momento da consulta este fuso horário está passando pelo horário de verão.

Testes com TIMESTAMP e TIMESTAMP WITH TIME ZONE no PostgreSQL

O PostgreSQL tem alguns comportamentos estranhos nas conversões. Meu banco está por padrão com o fuso horário 'America/Sao_Paulo'. A consulta abaixo pega exatamente o dia em que o horário de verão começou em 2017:

[code language="sql"]SELECT'Casting de texto para TIMESTAMP','2017-10-14 23:30'::TIMESTAMP,'2017-10-15 00:00'::TIMESTAMP,'2017-10-15 00:30'::TIMESTAMP,'2017-10-15 01:00'::TIMESTAMP,'2017-10-15 01:30'::TIMESTAMPUNION ALLSELECT'Casting de texto para TIMESTAMP, então adicionando uma timezone, e convertendo para a timezone UTC','2017-10-14 23:30'::TIMESTAMP AT TIME ZONE 'America/Sao_Paulo' AT TIME ZONE 'UTC','2017-10-15 00:00'::TIMESTAMP AT TIME ZONE 'America/Sao_Paulo' AT TIME ZONE 'UTC','2017-10-15 00:30'::TIMESTAMP AT TIME ZONE 'America/Sao_Paulo' AT TIME ZONE 'UTC','2017-10-15 01:00'::TIMESTAMP AT TIME ZONE 'America/Sao_Paulo' AT TIME ZONE 'UTC','2017-10-15 01:30'::TIMESTAMP AT TIME ZONE 'America/Sao_Paulo' AT TIME ZONE 'UTC'UNION ALLSELECT'Casting de texto para TIMESTAMP WITH TIME ZONE, e convertendo para a timezone UTC',('2017-10-14 23:30'::TIMESTAMP WITH TIME ZONE) AT TIME ZONE 'UTC',('2017-10-15 00:00'::TIMESTAMP WITH TIME ZONE) AT TIME ZONE 'UTC',('2017-10-15 00:30'::TIMESTAMP WITH TIME ZONE) AT TIME ZONE 'UTC',('2017-10-15 01:00'::TIMESTAMP WITH TIME ZONE) AT TIME ZONE 'UTC',('2017-10-15 01:30'::TIMESTAMP WITH TIME ZONE) AT TIME ZONE 'UTC'UNION ALLSELECT'Casting de texto para TIMESTAMP, então adicionando uma timezone, e convertendo para TIMESTAMP WITHOUT TIME ZONE',('2017-10-14 23:30'::TIMESTAMP AT TIME ZONE 'America/Sao_Paulo') ::TIMESTAMP WITHOUT TIME ZONE,('2017-10-15 00:00'::TIMESTAMP AT TIME ZONE 'America/Sao_Paulo') ::TIMESTAMP WITHOUT TIME ZONE,('2017-10-15 00:30'::TIMESTAMP AT TIME ZONE 'America/Sao_Paulo') ::TIMESTAMP WITHOUT TIME ZONE,('2017-10-15 01:00'::TIMESTAMP AT TIME ZONE 'America/Sao_Paulo') ::TIMESTAMP WITHOUT TIME ZONE,('2017-10-15 01:30'::TIMESTAMP AT TIME ZONE 'America/Sao_Paulo') ::TIMESTAMP WITHOUT TIME ZONEUNION ALLSELECT'Usando fórmula to_timestamp (para timestamp com timezone), e convertendo para a timezone UTC',to_timestamp('2017-10-14 23:30','YYYY-MM-DD HH24:MI') AT TIME ZONE 'UTC',to_timestamp('2017-10-15 00:00','YYYY-MM-DD HH24:MI') AT TIME ZONE 'UTC',to_timestamp('2017-10-15 00:30','YYYY-MM-DD HH24:MI') AT TIME ZONE 'UTC',to_timestamp('2017-10-15 01:00','YYYY-MM-DD HH24:MI') AT TIME ZONE 'UTC',to_timestamp('2017-10-15 01:30','YYYY-MM-DD HH24:MI') AT TIME ZONE 'UTC';[/code]

O resultado é:

O que percebemos com essa consulta é:

1. Quando pegamos um texto e fazemos cast para TIMESTAMP ele virará um TIMESTAMP WITHOUT TIME ZONE exatamente no horário do texto.
2. Quando pegamos um texto e fazemos cast para TIMESTAMP WITH TIME ZONE ele virará um TIMESTAMP WITH TIME ZONE exatamente no horário do texto mas com o fuso horário padrão do seu banco PostgreSQL.
3. Quando tempos um TIMESTAMP WITHOUT TIME ZONE e chamamos o AT TIME ZONE 'fuso', seu retorno será uma TIMESTAMP WITH TIME ZONE no 'fuso' informado (CUIDADO! Ao fazer um casting desse resultado com ::TIMESTAMP WITHOUT TIME ZONE o resultado poderá vir incorreto).
   Exemplo:

4. [code language="sql"]SELECT ('2018-04-14 18:00:00'::TIMESTAMP)AT TIME ZONE 'America/New_York';--Retornará "2018-04-14 19:00:00-03"--Tem algo esquisito nesse retorno,--mas vamos ver até onde isso vai...SELECT ('2018-04-14 18:00:00'::TIMESTAMP)AT TIME ZONE 'America/New_York'AT TIME ZONE 'UTC';--Retornará "2018-04-14 22:00:00"--Ok! O retorno foi o esperado.SELECT (('2018-04-14 18:00:00'::TIMESTAMP)AT TIME ZONE 'America/New_York')::TIMESTAMP WITHOUT TIME ZONE;--Retornará "2018-04-14 19:00:00"--Muito errado e estranho.[/code]

5. Quando temos um TIMESTAMP WITH TIME ZONE e chamamos o AT TIME ZONE 'fuso', seu retorno sempre será um TIMESTAMP WITHOUT TIME ZONE com o horário e data no 'fuso' informado.
   Exemplo:

   [code language="sql"]SELECT (('2018-04-14 18:00:00'::TIMESTAMP)AT TIME ZONE 'America/New_York')AT TIME ZONE 'America/New_York';--Retornará "2018-04-14 18:00:00"SELECT (('2018-04-14 18:00:00'::TIMESTAMP)AT TIME ZONE 'America/New_York')AT TIME ZONE 'America/Sao_Paulo';--Retornará "2018-04-14 19:00:00"SELECT (('2018-04-14 18:00:00'::TIMESTAMP)AT TIME ZONE 'America/New_York')AT TIME ZONE 'UTC';--Retornará "2018-04-14 22:00:00"[/code]

6. O PostgreSQL ignora das 00:00 até as 01:00 do dia em que iniciou o horário de verão. CUIDADO, se você escrever uma data neste intervalo inexistente de tempo, ele não apontará nenhum erro e retornará o equivalente à hora seguinte.

Referências

Trabalhando com Timestamps e Time Zones no PostgreSQL
https://phili.pe/posts/timestamps-and-time-zones-in-postgresql/

Código de teste:

WITH vars AS (	SELECT '2019-01-01'::TIMESTAMP AS ts, '2019-01-01'::TIMESTAMPTZ AS tz)SELECT ts, tz ,ts AT TIME ZONE 'America/Sao_Paulo', tz AT TIME ZONE 'America/Sao_Paulo',date_trunc('month',ts AT TIME ZONE 'America/Sao_Paulo'), date_trunc('month',tz AT TIME ZONE 'America/Sao_Paulo')FROM vars

Particularidades para o date_trunc
https://stackoverflow.com/questions/24040039/timezone-aware-date-trunc-function

SET timezone TO 'America/Sao_Paulo';SELECT '2019-01-01T00:00'::TIMESTAMPTZ;-- No fundo o PostgreSQL salva o valor em UTC mas ele converte na hora de exibir para o timezone 'America/Sao_Paulo'SET timezone TO 'America/Sao_Paulo';SELECT '2019-01-01T00:00'::TIMESTAMPTZ AT TIME ZONE 'America/Sao_Paulo';-- 2019-01-01 00:00:00 (without time zone)SET timezone TO 'America/Sao_Paulo';SELECT '2019-01-01T00:00'::TIMESTAMPTZ AT TIME ZONE 'UTC';-- 2019-01-01 02:00:00 (without time zone)-- Detalhes capciosos ao transformar TIMESTAMP em TIMESTAMPTZSET TIMEZONE TO 'America/Sao_Paulo';SELECT '2019-01-01T00:00:00'::TIMESTAMP AT TIME ZONE 'America/Sao_Paulo';Entrega um valor diferente de:SET TIMEZONE TO 'UTC';SELECT '2019-01-01T00:00:00'::TIMESTAMP AT TIME ZONE 'America/Sao_Paulo';-- TESTAR O DATE_TRUNC/*Ao usar date_part em um TIMESTAMPTZ, o resultado do date_part varia de acordo com o timezone do banco ou o timezone dado pela sessão.Como resolver esse problema?***Se estiver usando colunas timestamptz:Faça a consulta dando SET timezone TO 'timezone_desejado'.OU converta a coluna usando AT TIME ZONE (tz desejado), transformando-a para o horário local (do relógio) em formato TIMESTAMP (sem timezone).***Se estiver usando colunas timestamp:Se o timestamp estiver salvo em UTC e você quer fazer date_part em outro timezone:(Mudando o TimeZone do banco em tempo de sessão)SET TIMEZONE TO 'timezone desejado';SELECT '2019-01-01T00:00:00'::TIMESTAMP AT TIME ZONE 'timezone desejado';OU(Sem precisar mudar o TimeZone do banco em tempo de sessão)SET TIMEZONE TO 'UTC';SELECT '2019-01-01T00:00:00'::TIMESTAMP AT TIME ZONE 'timezone desejado' AT TIME ZONE 'timezone desejado';O que significa 2000-01-01 20:00:00+02?Significa       2000-01-01 18:00:00+00?*/

Exemplo de agregação com date_trunc

Imagine que temos a seguinte tabela, que mostra o horário em que uma compra foi feita e o valor da compra.

CREATE TABLE timestamp_testtimestamp TIMESTAMP NOT NULL, -- O timestamp de um acontecimento, salvo em UTC, embora a venda não tenha sido feita em UTCvalue NUMERIC NOT NULL, -- Um valor que foi dado a esse acontecimentoPRIMARY KEY (timestamp);

Quero criar estatísticas de compras, criando grupos por compras de cada dia, semana e mês.

Quero mostrar quantas compras foram feitas em cada dia, semana e mês.
Quero mostrar o somatório do valor das compras feitas em cada dia, semana e mês, mas dentro do fuso horário 'America/São_Paulo'.

SET TIMEZONE TO 'UTC'; -- Isso é opcional-- Você pode trocar 'month' por 'week' e 'day'SELECT COUNT(date_trunc('month',timestamp AT TIME ZONE 'America/Sao_Paulo' AT TIME ZONE 'America/Sao_Paulo')) number_of_rows_aggregated,date_trunc('month',timestamp AT TIME ZONE 'America/Sao_Paulo' AT TIME ZONE 'America/Sao_Paulo') timestamp_truncated,SUM(value) values_sumFROM timestamp_testGROUP BY (date_trunc('month',timestamp AT TIME ZONE 'America/Sao_Paulo' AT TIME ZONE 'America/Sao_Paulo'));