Exibindo e Inserindo data/hora no Postgres

Trabalhar com o tempo em bancos de dados não é moleza. O volume de erros cometidos é absurdamente grande. Difícil de imaginar algumas barberagens que a gente encontra por aí. Eu sei, muitos SGDBs não implementam tipos básicos como DATE, TIME, TIMESTAMP e INTERVAL do mesmo jeito. O estes tipos de dados foram definidos no ISO92, mas por razões históricas ou por incompetência mesmo, muitos não se ajustaram ao padrão. Por sorte, o Postgres tem ótima compatibilidade a quase todos os requisitos padrão ISO.

Bom, a confusão toda começa em se entender que ao se tratar do tempo, não interessa a forma como você vai exibir e sim o significado da informação. Bancos de dados relacionais são fortemente tipados. Isto é desejável se você quer garantir a integridade das informações. Isto ajuda na hora de indexar e recuperar a informação de forma velóz com base em critérios de seleção complexos. Isto ajuda a entender de forma mais clara o modelo de dados que você utiliza.

Então, dizer apenas ’92’ não me diz se você está falando do ano 1992, de 92 segundos, de 92 horas, 92 séculos, etc. Então, não importa de que forma você pretende exibir os dados, insira num formato mais padronizado possível. Se todas as pessoas armazenassem datas no formado ano/mês/dia o universo seria um lugar melhor. Mas cada um tem a mania de fazer isso num formato diferente. E você pode exibir datas no formato que quiser, mas se tem juízo, deveria sempre usar o mesmo formato:

# SELECT TIME '17:45:36';
   time
----------
 17:45:36
(1 row)

# -- Horário com precisão de milésimos de segundo
# SELECT TIME(3) '17:45:36.123';
     time
--------------
 17:45:36.123
(1 row)

# SELECT DATE '2012-12-18';
    date
------------
 2012-12-18

# -- Data antes de cristo (Before Christ)
# SELECT DATE '0050-12-18 BC';
     date
---------------
 0050-12-18 BC

# SELECT TIMESTAMP '2012-12-18 17:45:36';
      timestamp
---------------------
 2012-12-18 17:45:36
(1 row)

# -- Timestamp com precisão de milionésimos de segundo
# SELECT TIMESTAMP(6) '2012-12-18 17:45:36.123456';
        timestamp
--------------------------
 2012-12-18 17:45:36.1234
(1 row)

Note que não interessa se você acertou ou não os parâmetros DateStyle:

# SELECT TIMESTAMP '2012-12-18 17:45:36';
      timestamp
---------------------
 2012-12-18 17:45:36
(1 row)

# SELECT DATE '2012-12-18';
    date
------------
 2012-12-18

Também não muda se você ajustar o lc_time:

# SET lc_time='en_US.UTF8';
SET
# SELECT TIME(3) '11:45:36.123';
     time
--------------
 11:45:36.123
(1 row)

# SELECT TIME(3) '17:45:36.123';
     time
--------------
 17:45:36.123
(1 row)

# SET lc_time='pt_BR.UTF8';
SET
-- Utilizando a vírgula como separador decimal ao invés do ponto
# SELECT TIME(3) '17:45:36,123';
ERROR:  invalid input syntax for type time: "17:45:36,123"
LINE 1: SELECT TIME(3) '17:45:36,123';

Se você quiser saber exatamente como pode inserir data/hora no Postgres, as regras de interpretação do Postgres estão bem documentadas, mas existe ainda um último recurso para situações mais delicadas. Se você recebe os dados num formato específico, você pode forçar a interpretação correta do mesmo:

# SELECT to_date('18/12/2012','DD/MM/YYYY');
  to_date
------------
 2012-12-18
(1 row)

# SELECT to_timestamp('18/12/2012 17:45:53,123456','DD/MM/YYYY HH24:MI:SS,US');
         to_timestamp
-------------------------------
 2012-12-18 17:45:53.123456-02
(1 row)

Agora podemos exibir do jeito que a gente quiser a informação usando o nosso conhecido  to_char, e agora sim as variáveis de localização fazem algum efeito:

# SET lc_time='en_US.UTF8';
SET

# SELECT to_char(DATE'2012-12-18','Day/Month/YYYY');
         to_char
--------------------------
 Tuesday  /December /2012
(1 row)

-- Ajustando para exibir em Português do Brasil
# SET lc_time='pt_BR.UTF8';
SET

# SELECT to_char(DATE'2012-12-18','Day/Month/YYYY');
         to_char
--------------------------
 Tuesday  /December /2012

Opa, pera aí…. não funcionou. Sim, sim… tem uma pegadinha. Para ele traduzir, você precisa utilizar o prefixo TM. Note também que o seu sistema operacional tem de ter suporte para as diversas linguagens que você está utilizando. No meu caso eu ajustei o locales do meu Linux para suportar pt_BR, en_US, it_IT, es_ES e fr_FR:

-- Erro obtido quando o locales não está instalado:
# SET lc_time='ja_JP.UTF8';
ERROR:  invalid value for parameter "lc_time": "ja_JP.UTF8"
STATEMENT:  SET lc_time='ja_JP.UTF8';

# SET lc_time='it_IT.UTF8';
SET
# SELECT to_char(DATE'2012-12-18','TMDay/TMMonth/YYYY');
        to_char
-----------------------
 Martedì/Dicembre/2012
(1 row)

# SET lc_time='es_ES.UTF8';
SET
# SELECT to_char(DATE'2012-12-18','TMDay/TMMonth/YYYY');
        to_char
-----------------------
 Martes/Diciembre/2012
(1 row)

# SET lc_time='fr_FR.UTF8';
SET
# SELECT to_char(DATE'2012-12-18','TMDay/TMMonth/YYYY');
       to_char
---------------------
 Mardi/Décembre/2012
(1 row)

# SET lc_time='pt_BR.UTF8';
SET
# SELECT to_char(DATE'2012-12-18','TMDay/TMMonth/YYYY');
       to_char
---------------------
 Terça/Dezembro/2012
(1 row)

# SELECT to_char(DATE'2012-12-18','TMDy/TMMon/YYYY');
   to_char
--------------
 Ter/Dez/2012

# SET lc_time='en_US.UTF8';
SET
# SELECT to_char(DATE'2012-12-18','TMDay/TMMonth/YYYY');
        to_char
-----------------------
 Tuesday/December/2012
(1 row)

OK, agora você pode trazer muito mais que isso. Existem várias opções de formatação disponíveis:

# Exibindo: Século - Quartil, Semana do ano, Semana do Mês, Dia da semana e segundos após a meia-noite
SELECT to_char(current_timestamp,'CC - Q - WW - W - D - SSSS');
           to_char
-----------------------------
 21 - 4 - 51 - 3 - 3 - 72337
(1 row)

Bom, esta é só a ponta do Iceberg, inserir e exibir corretamente as datas. Estou prevendo pelo menos mais uns 2 posts para cobrir este vasto assunto um pouco melhor.

Até a próxima.