postgres postgresql timestamp timestamptz

O uso ideal de data e hora depende completamente da conversão correta de e para formatos usáveis por humanos (campos no front end e texto digitado) e um ponto na linha do tempo universal usado por todos. Uma vez convertido para essa representação interna, comparações, indexações, agrupamentos e junções são triviais pois operam sobre um número monotonicamente crescente e não ambíguo.

É óbvio que apenas as informações de data (yyyy-mm-dd) e hora (hh:mm:ss) não são suficientes para fazer a conversão correta de e para essa linha do tempo universal. Isso porque uma data e hora '2020-01-01 00:00:00' não representa um ponto no tempo, mas sim algumas centenas de possibilidades, que variam em função do local no planeta onde essa medição foi feita. Ou seja, '2020-01-01 00:00:00' em Brasília era um momento diferente de '2020-01-01 00:00:00' em Sidney.

Portanto, é absolutamente vital que o fuso horário seja carregado junto com data e hora, caso contrário seria impossível comparar corretamente dois momentos no tempo. Qualquer representação, armazenamento ou comparação que não envolver os três dados traz o potencial de carregar erros de lógica e perdas de dados, tanto no banco de dados quanto na aplicação. Além disso, supor um fuso horário implícito, como UTC, é a causa de diversos heisenbugs difíceis de identificar e corrigir.

O padrão internacional ISO 8601 define um formato completo e não-ambíguo de texto para representação de data, hora e fuso horário. Ele deve ser usado para toda entrada e saída de data e hora que se der por formato de texto.

A não observância desse padrão faz com que a aplicação precise converter de e para diversos formatos entre seus componentes e softwares de terceiros, como o banco de dados. Essas conversões, além de serem custosas computacionalmente, também introduzem débito técnico na forma de riscos de erros de lógica e perda de dados. Exemplo de conversão arriscada:

    to_timestamp('...', 'DD/MM/YYYY')

e

    to_timestamp('...', 'Mon DD YYYY HH24:MI')

O ISO 8601 deve também, obrigatoriamente, incluir o campo de fuso horário. Mesmo quando o fuso horário seria UTC, ele deve ser informado como +00 ou UTC, como '2020-01-01 00:00:00+00', '2020-01-02 00:00:00Z' ou '2020-01-01 00:00:00 UTC'.

A omissão do fuso horário faz com que a data e hora sejam interpretadas em fuso horário 'local', o que permite ambiguidade na comunicação, gerando dados potencialmente errados na entrada e saída de todos os componentes da aplicação, especialmente quando cada servidor da sua infraestrutura tem um fuso horário 'local' diferente, e todos possivelmente diferentes do fuso horário do usuário final.

Exemplo de código com risco de comparação potencialmente incorreta por omissão de fuso horário:

    WHERE data_inicio < to_timestamp('...', 'YYYY-MM-DD HH24:MI')

O padrão SQL define o tipo de dados timestamp (without timezone) como armazenando data e hora, mas ignorando a existência de fuso horários. Isso significa que tem o potencial de causar problemas de entrada e saída. O PostgreSQL implementa o padrão SQL, mas desaconselha o uso desse tipo de dados.

Por exemplo, os textos '2020-01-01 00:00:00+03' e '2020-01-01 00:00:00+01' representam horários diferentes (com 2h de diferença) e, portanto, pontos diferentes na linha do tempo. Contudo, a especificação diz que o fuso horário é descartado e então eles são tratados como iguais pelo tipo de dados timestamp (without timezone), como:

[[local]:5432] postgres@postgres=# select   '2020-01-01 00:00:00+03'::timestamp
[[local]:5432] postgres@postgres-#        = '2020-01-01 00:00:00+01'::timestamp as "iguais?";
╔═════════╗
║ iguais? ║
╠═════════╣
║ t       ║
╚═════════╝

Isso causa erros de lógica e força que a aplicação faça conversões extras (de e para UTC, por exemplo) para contornar eles, cuja execução, validação e testes são onerosos e falíveis. Mesmo se a aplicação conseguir implementar todas essas conversões, o resultado não é melhor que o uso de timestamp with timezone (a seguir).

Esse tipo de dados existe apenas por compatibilidade com o padrão SQL e com sistemas legados, mas não deve ser usado para outros casos.

Dado que a única forma correta de tratamento de tempo é pelo tratamento correto do trio data, hora e fuso horário sempre juntos, convertendo as entradas e saídas de texto adequadamente, o PostgreSQL implementa o tipo de dados timestamptz (também chamado de timestamp with timezone) justamente com essa preocupação.

O timestamptz é um número inteiro de 8 bytes representando microssegundos desde '1970-01-01 00:00:00.000000+00' (UTC), assim como seu irmão incorreto, o timestamp (without timezone). Mas diferente do outro tipo de dados, o timestamptz faz a conversão correta de e para formatos de texto externos, o que permite identificar que, por exemplo, as datas a seguir são distintas:

[[local]:5432] postgres@postgres=# select   '2020-01-01 00:00:00+03'::timestamptz
[[local]:5432] postgres@postgres-#        = '2020-01-01 00:00:00+01'::timestamptz as "iguais?";
╔═════════╗
║ iguais? ║
╠═════════╣
║ f       ║
╚═════════╝

E que as datas a seguir são iguais e representam o mesmo momento em dois pontos diferentes do planeta:

[[local]:5432] postgres@postgres=# select   '2020-01-01 03:00:00+03'::timestamptz
[[local]:5432] postgres@postgres-#        = '2020-01-01 01:00:00+01'::timestamptz as "iguais?";
╔═════════╗
║ iguais? ║
╠═════════╣
║ t       ║
╚═════════╝

TODO: Um caso de uso para timestamp without timezone + text é lidar com mudanças futuras no banco de dados de fuso horário, mas a explicação é longa e repleta de detalhes, então deixo para escrever no futuro.

Como via de regra, sempre use o tipo de dados timestamptz e nunca use o tipo de dados timestamp. Quando comunicando e armazenando data, hora e fuso horário por texto, sempre use o formato ISO 8601 completo. Se achar melhor converter para UTC antes de armazenar ou comunicar, é sinal de que está fazendo coisa errada.

https://wiki.postgresql.org/wiki/Don%27t_Do_This#Date.2FTime_storage