Entendendo datatypes: DATE e TIMESTAMP

Objetivo: Entender a diferença entre os datatypes DATE e TIMESTAMP 

1)      De quando a quando o Oracle armazena datas:

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

2)      Com isso:

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

3)      SYSDATE X CURRENT_DATE

SYSDATE: retorna a data e horário do servidor

CURRENT_DATE: retorna a data e horário de acordo com o fuso horário da sessão do usuário

Como alterar o current_date ?

Alterando o parâmetro time_zone que pode ser definido como:
– um deslocamento absoluto (‘-05:00’),
– um fuso horário local do sistema operacional (local),
– definido como o dbtimezone
– determinada região ‘America/New York’

        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
            Esse caso representa o horário do Brasil (Brasília) e horário de Nova York.

4)      O parâmetro time_zone é apenas  um parâmetro de sessão e não um parâmetro de   inicialização, ele é sensível ao fuso horário da sessão.

               O DBA define o fuso horário default do banco de dados, especificando a clausula SET         TIME_ZONE (Win) ou export TIME_ZONE (Unix/Linux) durante a instalação. Caso não seja especificado o fuso horário do banco passa a ser o mesmo do Sistema Operacional.

A função que retorna o valor do fuso horário do banco de dados é DBTIMEZONE.

O fuso horário para a sessão e para o banco de dados podem ser definidas diferentemente, o parâmetro sessiontimezone define o fuso horário da sessão:

SQL> select sessiontimezone from dual ;

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

SQL> select dbtimezone from dual ;

DBTIME
——
+00:00

5)      O tipo de dados timestamp foi introduzido a partir do Oracle9i e é uma extensão do tipo DATE. Armazena ano, mês, dia, horas, minutos e segundos e é capaz de armazenar o valor das frações de segundo. A precisão dessas frações pode ser um número de 0 a 9, e o valor default é 6.

Como verificar: (Se Não definimos o tamanho ao criar o valor default ficou 6)

create table t2 (ex_dados timestamp);

SQL> desc t2

     Nome            Nulo?    Tipo
     ————— ——– ——————————————————–
     EX_DADOS                                                    TIMESTAMP(6)

    SQL> insert into t2 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 t2;

    EX_DADOS
    ————————–
    05/08/2009 08:20:32.627869
       Há alguma função que mostre um valor do tipo TIMESTAMP ?
       R: Sim. LOCALTIMESTAMP

    SQL> select LOCALTIMESTAMP from dual ; 

    LOCALTIMESTAMP
    ————————————————–
    24/01/11 17:13:06,702000

6) Há 2 tipos de TIMESTAMP:

                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 é a diferença em horas e minutos entre o horário local e o GMT.

Resumo: dois valores TIMESTAMP WITH TIME ZONE serão considerados idênticos se representarem o mesmo instante em GMT, independente dos deslocamentos de fusos horários (TIME ZONE) armazenados nos dados:

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.

TIMESTAMP WITH A 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, significa 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:

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′
Para mostrar os TIMESTAMP e suas variações podemos mostrar da seguinte forma:

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 8:20:54,656000 -03:00

Datatype Interval:

SQL> CREATE TABLE MANUTENCAO(
  2    DT_INICIO DATE,
  3    TMP_PARALIZACAO INTERVAL DAY TO SECOND 
4  )
  5  /

Tabela criada.

SQL> INSERT INTO MANUTENCAO VALUES ( SYSDATE+5, INTERVAL ‘0 02:10:00′ DAY TO SECOND )
  2  /

1 linha criada.

 SQL> commit ;

 Commit concluÝdo.

 SQL> select * from manutencao ;

 DT_INICI TMP_PARALIZACAO
——– —————————————————————————
29/01/11 +00 02:10:00.000000

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

SessÒo alterada.

 SQL> select * from manutencao ; 

DT_INICIO        TMP_PARALIZACAO
—————- —————————————————————————29/01/2011 22:37 +00 02:10:00.000000

 SQL> SELECT DT_INICIO+TMP_PARALIZACAO  FROM MANUTENCAO;

 DT_INICIO+TMP_PA
—————-
30/01/2011 00:47

Espero que este exemplo prático com todas as varíaveis de date e timestamp existentes no Oracle ajude a perceber as diferenças entre esses importantes datatypes.

Sobre oradeep

José Eduardo Fiamengui Júnior Graduação: Tecnologia em Informática pela Universidade Estadual de Campinas (Unicamp) Pós-Graduação: Administração em Banco de Dados Oracle pelo Instituto Brasileiro de Tecnologia Avançada (IBTA) Mba em Gestão Estratégica em TI pela FGV OCE Certified ITIL Certified Empresa Atual: Dba Oracle e Performance Specialist na Ccee Empresa Atual: Instrutor Oracle IBTA
Esta entrada foi publicada em Uncategorized. ligação permanente.

Deixe uma Resposta

Preencha os seus detalhes abaixo ou clique num ícone para iniciar sessão:

Logótipo da WordPress.com

Está a comentar usando a sua conta WordPress.com Terminar Sessão / Alterar )

Imagem do Twitter

Está a comentar usando a sua conta Twitter Terminar Sessão / Alterar )

Facebook photo

Está a comentar usando a sua conta Facebook Terminar Sessão / Alterar )

Google+ photo

Está a comentar usando a sua conta Google+ Terminar Sessão / Alterar )

Connecting to %s