DATE datatype – Representação interna

Em discussão hoje sobre um tema interessante (timezones) com alguns colegas de trabalho, surgiu a pergunta de como as BDs em geral guardam os formato tipo DATE. Neste post abordarei apenas o Oracle.
Para iniciar vamos criar uma tabela com uma data:

SQL>  create table rep_dates as select to_date('04-07-2011 12:00:00', 'dd-mm-yyyy hh24:MI:SS') d1 from dual;

Table created.

 

Para entendermos como o Oracle guarda internamente os dados que teem o datatype DATE, vamos usar o função DUMP
que simplesmente retorna um varchar2 com o código do datatype, o tamanho e a representação interna do valor.
Aplicando a teoria temos:

SQL> select dump(d1) from rep_dates;

DUMP(D1)
--------------------------------------------------------------------------------
Typ=12 Len=7: 120,111,7,4,13,1,1

Facilmente percebemos que o código associado ao datatype é 12,o tamanho são 7 bytes e a representação interna 120,111,7,4,13,1,1

Uma análise cuidada ao dump efectuado percebemos o seguinte:

* 1º byte (120) representa o século. Contém um offset de 100 para assegurar que o Oracle suporta a notação AC/BC.
* 2º byte (111) representa o Ano. 111 = 100 + 11. Tal como o século contém um offset de 100 para suportar a notação AC/BC
* 3º byte (7) representa o mês
* 4º byte (4) representa o dia
* 5º byte (13) representa a hora com um offset de 1 para evitar que seja possivel colocar NULLs, sendo sempre o valor mínimo
de 1.
* 6º byte (1) representa os minutos com offset de 1
* 7º byte (1) representa os minutos com offset de 1

Para retirar as dúvidas que possa restar, vamos inserir outra data, parecida com a anterior, mas com o ano de 1011:


SQL> insert into rep_dates select to_date('04-07-1011 12:00:00', 'dd-mm-yyyy hh24:MI:SS') d1 from dual;

1 row created.

DUMP(D1)
——————————————————————————–
Typ=12 Len=7: 120,111,7,4,13,1,1
Typ=12 Len=7: 110,111,7,4,13,1,1

 

Temos as duas representações lado a lado e observa-se que a representação do ano é igual mesmo tendo modificado o ano. Acontece pois a distinção é feita com recurso ao século (110), ou seja, sabendo que estamos no século XI o unico ano possível que termina em 11 (+offset) é 1011. Fácil 🙂

Ligando a máquina do tempo, vamos inserir uma data BC (sendo o limite do Oracle 4712BC):


SQL> insert into rep_dates select to_date('04/07/-1011 12:00:00', 'dd/mm/syyyy hh24:mi:ss') d1 from dual;

1 row created.

É importanto usar a notação correcta (uso do syyyy). Executando o dump temos:

 

DUMP(D1)
--------------------------------------------------------------------------------
Typ=12 Len=7: 120,111,7,4,13,1,1 (corresponde a 04-07-2011)
Typ=12 Len=7: 110,111,7,4,13,1,1 (corresponde a 04-07-1011)
Typ=12 Len=7: 90,89,7,4,13,1,1 (corresponde a 04-07-1011 BC)

 

Com este exemplo, percebemos a utilidade do offset (+100) e para que foi definido na representação interna. A possibilidade de definir datas BC como neste exemplo complica-nos um pouco as contas mas temos que:

Século 90 = 100 – 90 = século 10 (+1?).
Ano 89 = 100 – 89 = ano 11.

Como anteriormente no século 11 BC o único ano possível terminado em 11 será 1011 BC.

 

Espero ter sido claro. No entanto deixei para o fim algo que não consegui descobrir e uma ajuda é sempre bem vinda, ou seja, na definição do século no ano de 2011 (ou em qualquer ano), a representação é 120. Com +100 de offset o século dá XX e não XXI. Deve haver algo que desconheça na contagem dos séculos 🙂

Advertisements

One thought on “DATE datatype – Representação interna

  1. Boa Luis!mais um post muito interessante. Tens um blog muito fixe!Eu acho que sei porque é que na definição do ano 2011 tens 120 no século:se pensares de uma forma matemática essa é a forma coerente de representar os números. Repara que o mesmo acontece com todas as restantes ordens de grandeza diferentes como a hora, minuto e segundo. Exemplo:às 13h40m, apesar de ser a 14ª hora do dia, tu dizes/escreves 13.Ou seja, tu representas os números como uma soma que obedece ao critério do maior múltiplo comum (tal como a distância e as outras grandezas…).Se não fosse assim, imagina que querias representar coisas tipo “recuar 3000 anos”, ou seja, a operação aritmética século 20 – 10. Fica:120-30 = 90 o que se traduz em 1000BC (certo)caso contrário seria 130 – 30 = 100 ou seja “Nascimento de Jesus Cristo”, mas ambos sabemos que o resultado é -1000 anos…No fundo, nós em português é que violamos a regra por adoptarmos ao calendário gregoriano.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s