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 qual a lógica bizarra por trás, e aqui vai minhas anotações sobre esse estudo.

O PostgreSQL têm salvo em um lugar que existe um fuso horário (time zone) padrão para todas as suas consultas.
Para descobrir esse esse fuso horário, execute:
SHOW timezone;

Se esta timezone estiver “errada” você tem duas opções.
1) Executar o seguinte comando TODA VEZ que você se conectar/reconectar ao banco de dados:

SET TIME ZONE 'Continente/Cidade';

2) Mudar para sempre o fuso horário nos arquivos de configuração:
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.

Se quiser escolher uma nova timezone, coloque na linha algum valor da coluna name desta tabela:

 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 (essa abreviação não é única para cada name, portanto muito cuidado! 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:

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'::TIMESTAMP
UNION ALL
SELECT
'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 ALL
SELECT
'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 ALL
SELECT
'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 ZONE
UNION ALL
SELECT
'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'
;

O resultado é:

Casting de texto para TIMESTAMP 14/10/17 23:30 15/10/17 00:00 15/10/17 00:30 15/10/17 01:00 15/10/17 01:30
Casting de texto para TIMESTAMP, então adicionando uma timezone, e convertendo para a timezone UTC 15/10/17 02:30 15/10/17 03:00 15/10/17 03:30 15/10/17 03:00 15/10/17 03:30
Casting de texto para TIMESTAMP WITH TIME ZONE, e convertendo para a timezone UTC 15/10/17 02:30 15/10/17 03:00 15/10/17 03:30 15/10/17 03:00 15/10/17 03:30
Casting de texto para TIMESTAMP, então adicionando uma timezone, e convertendo para TIMESTAMP WITHOUT TIME ZONE 14/10/17 23:30 15/10/17 01:00 15/10/17 01:30 15/10/17 01:00 15/10/17 01:30
Usando fórmula to_timestamp (para timestamp com timezone), e convertendo para a timezone UTC 15/10/17 05:30 15/10/17 03:00 15/10/17 03:30 15/10/17 03:00 15/10/17 03:30

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. 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.
    
  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:

    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"
    
  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.

Deixe uma resposta

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *