Introdução ao tipo de dado TIMESTAMP do Oracle e suas variações (WITH TIME ZONE e WITH LOCAL TIME ZONE) – Guia completo

Introdução ao tipo de dado TIMESTAMP e suas variações

O tipo de dado DATE do Oracle é um tipo especial capaz de armazenar datas que vão de 4712 a.C. a 9999 d.C, mas além de armazenar informações de século, ano, mês e dia, o mesmo também é capaz de armazenar informações de hora, minuto e segundo. Mas aí você poderia perguntar: Se o tipo de dado DATE é capaz de manter essas informações temporais, isso não poderia ser considerado um TIMESTAMP? Não necessariamente. O tipo de dados TIMESTAMP introduzido à partir do Oracle 9i, vai muito mais além do que simplesmente armazenar de forma “crua” informações de data e horários. Podemos dizer que o tipo de dado TIMESTAMP é uma extensão do tipo de dados DATE, capaz de manter informações de tempo com maior precisão. Faremos uma breve introdução a este tipo de dado e suas variações (WITH TIME ZONE e WITH LOCAL TIME ZONE) que, muitas vezes, é fonte de dúvida entre desenvolvedores e administradores de banco de dados quanto à sua aplicação e uso.

Antes de começar a descrever sobre o tipo de dados TIMESTAMP, veremos através do exemplo abaixo, como poderíamos verificar informações de século, data e horários atuais, selecionando-as direto do banco de dados através do uso da função SYSDATE:

SQL> select to_char(to_date(0,'J'), 'dd/mm/yyyy ad') from dual;
select to_char(to_date(0,'J'), 'dd/mm/yyyy ad') from dual
*
ERRO na linha 1:
ORA-01854: data juliana deve estar entre 1 e 5373484

SQL> select
2  to_char(to_date(1,'J'), 'dd/mm/yyyy ad') de,
3  to_char(to_date(5373484,'J'), 'dd/mm/yyyy ad') ate
4  from dual;

DE              ATE
--------------- ---------------
01/01/4712 a.C. 31/12/9999 d.C.

SQL> select to_char(sysdate,'cc dd/mm/yyyy hh24:mi:ss') data from dual;

DATA
----------------------
21 17/08/2009 08:10:25

Talvez você também já tenha ouvido falar da função CURRENT_DATE. A diferença entre ela e o SYSDATE é que a função SYSDATE retorna a data e horário do servidor, enquanto que a função CURRENT_DATE retorna a data e horário de acordo com o fuso horário da sessão do usuário. Para comprovar tal explicação, no exemplo abaixo irei alterar o fuso horário da minha sessão para o fuso horário de Nova York nos EUA que é ‘-5:00’. No servidor Oracle, o fuso horário está definido em ‘-3:00’, ou seja, três horas de atraso em relação à hora de Greenwich.

SQL> alter session set nls_date_format='dd/mm/yyyy hh24:mi:ss';

Sessão alterada.

SQL> select sessiontimezone from dual;

SESSIONTIMEZONE
----------------
-03:00

SQL> alter session set time_zone='-5:00';

Sessão alterada.

SQL> select sessiontimezone from dual;

SESSIONTIMEZONE
----------------
-05:00

SQL> select sysdate,current_date from dual;

SYSDATE             CURRENT_DATE
------------------- -------------------
17/08/2009 08:15:45 17/08/2009 06:15:45

Podemos perceber acima que SYSDATE retornou o horário do servidor, e CURRENT_DATE o horário de acordo com o fuso horário definido na minha sessão.

Bom, voltando ao assunto que originou este artigo e, como já dito anteriormente, o tipo de dados TIMESTAMP introduzido à partir do Oracle 9i é uma extensão do tipo de dados DATE. Além de armazenar o ano, mês, dia, horas, minutos e segundos do tipo de dados DATE, o mesmo é capaz também de armazenar o valor das frações de segundo. A precisão dessas frações, como o próprio nome já diz, nada mais é do que o número de dígitos da parte fracionária dos “segundos”. Esta precisão pode ser um número de 0 a 9, mas quando não especificado, o seu valor padrão é 6.

SQL> create table t1 (ts_data timestamp);

Tabela criada.

SQL> desc t1
Nome                         Nulo?    Tipo
---------------------------- -------- ------------------------
TS_DATA                               TIMESTAMP(6)

SQL> insert into t1 values (localtimestamp);

1 linha criada.

SQL> alter session set nls_timestamp_format = 'dd/mm/yyyy hh24:mi:ss.ff';

Sessão alterada.

SQL> select * from t1;

TS_DATA
--------------------------
05/08/2009 08:20:32.627869

Além do exemplo demonstrado acima, existem ainda duas variações do tipo de dados TIMESTAMP:

O tipo TIMESTAMP WITH TIME ZONE é uma variante de TIMESTAMP que inclui um deslocamento de fuso horário (TIME ZONE) em seu valor. Podemos dizer que o deslocamento de fuso horário em questão é a diferença em horas e minutos entre o horário local e o UTC. UTC é um acrônimo do Inglês para “Coordinated Universal Time” ou “Tempo Universal Coordenado” que corresponde ao fuso horário de referência a partir do qual se calculam todas as outras zonas horárias do mundo. Segundo algumas bibliografias, o UTC é o sucessor do Tempo Médio de Greenwich (Greenwich Mean Time), abreviadamente mais conhecido como GMT. Esta nova denominação foi cunhada para basear a medida do tempo nos padrões atômicos, mais do que nos celestes. Em resumo, dois valores TIMESTAMP WITH TIME ZONE serão considerados idênticos se representarem o mesmo instante em UTC, independente dos deslocamentos de fusos horários (TIME ZONE) armazenados nos dados. Confuso? Bom, para que não haja dúvidas, poderemos ver abaixo um exemplo que demonstra uma informação de data e horário que deverá ser reunida ou coordenada entre diferentes regiões geográficas com fusos horários distintos:

Por exemplo:

TIMESTAMP ’17/08/2009 08:00:00 -3:00′

é o mesmo que

TIMESTAMP ’17/08/2009 06:00:00 -5:00′

Isto é, 08:00 horas da manhã no horário de Brasília é o mesmo que 06:00 horas da manhã no horário de Nova York nos EUA.

O outro tipo, TIMESTAMP WITH LOCAL TIME ZONE é outra variante de TIMESTAMP que inclui um deslocamento de fuso horário em seu valor. A diferença é que este deslocamento não é armazenado como parte dos dados da coluna, mas sim, normalizado para o fuso horário do banco de dados (DBTIMEZONE). O verbo “normalizar”, neste caso, eu entendo como um cálculo que é realizado tendo como base o fuso horário definido no banco de dados afim de se mostrar o horário local do usuário. Portanto, o Oracle retornará a informação de data, hora, minuto e segundo no fuso horário local da sessão que está acessando o dado. Vale a pena salientar que o uso desta variante é apropriado para aplicações onde se deseja exibir as datas e os horários usando o fuso horário do sistema cliente. Para quem já acessou algum tipo fórum ou lista de discussão de nível global, notará que ao postar algum tópico no fórum, a data e horário mostrada será convertida para o fuso horário local. Caso um leitor que esteja no Japão acessar o tópico, verá a data e horário de acordo com o fuso horário do Japão. Então, utilizando como base o exemplo anterior:

Supondo que eu esteja no Brasil (fuso horário -3:00) e insira a data e horário abaixo:

TIMESTAMP ’17/08/2009 08:00:00′

e um usuário que esteja em Nova York (fuso horário -5:00) acessar o mesmo dado, a data apresentada para este usuário será TIMESTAMP ’17/08/2009 06:00:00′

No mais, para demonstrar o uso do tipo TIMESTAMP e suas duas variações, irei realizar algumas operações abaixo. Vale a pena salientar que o Oracle possui além das funções SYSDATE e CURENT_DATE, outras funções embutidas para capturar informações de data e horários atuais como, LOCALTIMESTAMP, CURRENT_TIMESTAMP e SYSTIMESTAMP onde:

• LOCALTIMESTAMP: retorna um valor do tipo TIMESTAMP como data e horário corrente (incluindo frações de segundos), de acordo com o fuso horário definido na sessão do usuário.
• CURRENT_TIMESTAMP: retorna um valor do tipo TIMESTAMP WITH TIME ZONE como data e horário corrente (incluindo frações de segundos), de acordo com o fuso horário definido na sessão do usuário.
• SYSTIMESTAMP: retorna um valor do tipo TIMESTAMP WITH TIME ZONE como data e horário corrente (incluindo frações de segundos), de acordo com o fuso horário definido no sistema onde o servidor de banco de dados reside.

Para demonstrar o resultado das funções acima, executarei os comandos SQL abaixo:

SQL> alter session set time_zone='-5:00';

Sessão alterada.

SQL> select localtimestamp,current_timestamp,systimestamp from dual;

LOCALTIMESTAMP           CURRENT_TIMESTAMP               SYSTIMESTAMP
------------------------ ------------------------------- -------------------------------
17/08/09 06:20:54,656000 17/08/09 06:20:54,656000 -05:00 17/08/09 08:20:54,656000 -03:00

Podemos perceber pelo resultado acima que LOCALTIMESTAMP e CURRENT_TIMESTAMP apresentaram o mesmo horário, a diferença é que CURRENT_TIMESTAMP trouxe a informação do fuso horário (-5:00). Já a função SYSTIMESTAMP, diferente de CURRENT_TIMESTAMP, trouxe o horário de acordo com o fuso horário definido no sistema do servidor de banco de dados.

Irei agora criar uma tabela de teste conforme abaixo:

SQL> create table t2 (
2   a timestamp,
3   b timestamp with time zone,
4   c timestamp with local time zone
5  );

Tabela criada.

SQL> desc t2
Nome                      Nulo?    Tipo
------------------------- -------- ---------------------------------
A                                  TIMESTAMP(6)
B                                  TIMESTAMP(6) WITH TIME ZONE
C                                  TIMESTAMP(6) WITH LOCAL TIME ZONE

SQL> insert into t2 values (localtimestamp,localtimestamp,localtimestamp);

1 linha criada.

SQL> select * from t2;

A                        B                               C
------------------------ ------------------------------- ------------------------
17/08/09 08:30:10,250000 17/08/09 08:30:10,250000 -03:00 17/08/09 08:30:10,250000

Independente da função utilizada, as informações de data e horários foram inseridas nas devidas colunas e mostram o mesmo horário. Agora irei realizar através de uma aplicação (no meu caso o SQL*Plus), a simulação do acesso remoto ao banco de dados na qual o usuário está localizado em uma região geográfica onde fuso horário desta região seja diferente como, por exemplo, Nova York nos EUA:

SQL> alter session set time_zone='-5:00';

Sessão alterada.

SQL> select dbtimezone,sessiontimezone from dual;

DBTIMEZONE SESSIONTIMEZONE
---------- ---------------
-03:00     -05:00

SQL> select * from t2;

A                         B                              C
------------------------ ------------------------------- ------------------------
17/08/09 08:30:10,250000 17/08/09 08:30:10,250000 -03:00 17/08/09 06:30:10,250000

De acordo com o resultado acima, podemos notar que os resultados das colunas A e B não se alteraram, ou seja, como o usuário Nova Yorkino não tem conhecimento do fuso horário da região de onde está localizado o banco de dados, o valor da coluna A seria um tanto impreciso. Já o valor da coluna B faria mais sentido pelo fato de apresentar a informação do fuso horário, ou seja, o usuário saberia de forma precisa, o horário em que o dado foi gravado. Por último, podemos perceber que o valor da coluna C foi automaticamente “convertido” para o fuso horário da sessão do usuário que, neste caso é ‘-5:00’.

Concluindo, o tipo de dado TIMESTAMP WITH LOCAL TIME ZONE é ideal e apropriado para aplicações onde se deseja exibir informações de data e horários usando o fuso horário do sistema cliente.