Crie um site como este com o WordPress.com
Comece agora

Como datas & horas são armazenadas no SQL Server?

Com frequência tenho visto dúvidas sobre como o SQL Server armazena colunas com tipo de dados que contenha data e/ou hora. Recentemente li em tópico de fórum alguém mencionando que a data é armazenada de acordo com o idioma do sistema operacional. 😮

Se (não) é assim, então como são armazenados data & horário no banco de dados?

1. Introdução

Quando iniciei meus estudos em SQL Server, a forma como utilizar datas foi para mim um ponto nebuloso. O formato em que o Management Studio retorna expressões do tipo data & horário pode levar iniciantes em T-SQL a deduzir que a data fique armazenada no formato “yyyy-mm-dd”. Além do Management Studio, outro fator desse engano é que na documentação dos tipos de dados relacionados a data & horário a data é sempre informada no formato “yyyy-mm-dd”. Há um motivo para isso mas não tem qualquer relação com o formato de armazenamento.

Com elevada frequência tenho visto dúvidas sobre como o SQL Server armazena colunas com tipo de dados que contenha data e/ou hora, além de afirmações completamente equivocadas. Recentemente li em tópico de fórum alguém mencionando que a data é armazenada de acordo com o idioma do sistema operacional. 😮
Se (não) é assim, então como são armazenados data & horário no banco de dados? A resposta é: depende do tipo de dados.

Neste artigo referências a data estão no formato “dd/mm/yyyy”, que é o formato padrão no Brasil. O texto deste artigo utiliza com frequência a representação hexadecimal de um valor originalmente em binário. Caso não tenha conhecimento sobre esse sistema, sugiro a leitura prévia do verbete Sistema de numeração hexadecimal, na wikipedia.

2. Tipos de dados data & hora

Neste capítulo estão abordados os seguintes tipos de dados: datetime, datetime2, datetimeoffset, time e date. O seguinte quadro compara as principais características de cada um dos tipos de dados de data e/ou hora:

p028_quadro 1

2.1. datetime

O tipo de dados datetime permite armazenar data e horário, mas sem registro de fuso horário. Possui baixa precisão, dispondo de 3 casas fracionárias de segundo:

Faixa de valores:
01/01/1753 00:00:00.000 a 31/12/9999 23:59:59.997

O tipo de dados datetime utiliza 8 bytes para armazenamento, sendo que os 4 bytes iniciais (da esquerda para a direita) armazenam a data e os 4 bytes finais armazenam o horário:

p028_bytes datetime

Data. No caso da parte de data, o valor 0 indica a data 1/1/1900; se o valor é positivo, significa número de dias a somar à data de 1/1/1900 e se for negativo é o número de dias a subtrair de 1/1/1900.

p028_quadro 2

Observe como o valor da coluna “hexadecimal (data)”, que é a representação em hexadecimal do valor armazenado na parte de data, passa de 0 para 16D (365 em decimal) quando se acrescenta um ano: 365 dias a mais. Para gerar o resultado do quadro acima, bem como outros valores, você pode rodar o código #1, disponível ao final deste artigo.

Horário. Com relação ao horário é algo semelhante, sendo que o valor 0 significa “00:00:00.000”. A cada 1 tick que é acrescentado, 1/300 de segundo é acrescentado no horário.

p028_quadro 3

Para gerar o resultado do quadro acima, bem como outros valores, você pode rodar o código #2, disponível ao final deste artigo.

Como prova dos noves basta efetuar o seguinte cálculo:

SELECT convert (char(12),
                dateadd (ms, 
                        ticks * cast(1 as float) / 300 * 1000,
                        '0:00:00'),
                114) as [Prova dos 9];

onde o número de ticks é multiplicado por 1 / 300 ms.

Você deve ter observado no quadro 3 que os valores de horários terminam em 0, 3 ou 7. O motivo é que não é possível terminar com outros valores, por causa da precisão do tipo de dados datetime. Isto é demonstrado na seguinte tabela:

p028_quadro 4

Esta característica negativa do tipo de dados datetime é tratada no artigo “Para onde foi o 23:59:59.999?!”.

Exercício de fixação. Considerando-se o seguinte valor, 00|00|AB|64|00|B5|46|40, e que ele representa o conteúdo de uma coluna do tipo datetime, qual é a data e horário?

2.2. datetime2

O tipo de dados datetime2 permite armazenar data e horário, mas sem registro de fuso horário. Possui alta precisão (100 nanossegundos), permitindo definir até 7 casas fracionárias de segundo:

Faixa de valores:
01/01/0001 00:00:00.0000000 a 31/12/9999 23:59:59.9999999

Este tipo de dados foi implementado na versão 2008 do SQL Server. Uma característica do tipo de dados datetime2 é que a precisão da parte fracionária pode ser ajustada, indo de 0 a 7 algarismos. Por causa disso que o espaço físico ocupado varia de 6 a 8 bytes, dependendo justamente da precisão escolhida: 0 a 2 algarismos: 6 bytes; 3 ou 4 algarismos: 7 bytes; e 5 a 7 algarismos: 8 bytes.

p028_bytes datetime2

Data. Independente do tamanho ocupado na tabela, os 3 bytes iniciais (numerados de 1 a 3 na figura acima) armazenam o valor numérico da data, sendo que o valor 0 (zero) representa a data “1/1/0001”; o valor 1 (um) representa a data “2/1/0001”; e assim em diante:

p028_quadro 5

No quadro 5, montado a partir do resultado do código #3, os bytes estão separados pelo caractere “|” e os bytes que armazenam a data estão sublinhados. Quando um valor do tipo datetime2 é convertido para binary, um byte extra é acrescentado à esquerda do conjunto original, para informar a precisão. É por este motivo que na coluna hexadecimal há 9 bytes (ou 18 algarismos base 16).

Ao analisar o conteúdo da coluna hexadecimal observa-se que o byte menos significativo (low order) é o primeiro à esquerda, o que significa que utiliza little endian. Como citado anteriormente, a data ocupa os 3 bytes à direita. Para converter de little endian para big endian é necessário obter os 3 bytes, inverter a ordem deles e então obter o valor de tick de dias.
Para a data de 1/1/0002 obtém-se o valor 365 na coluna Ticks, que é o número de dias a somar à data base (1/1/0001). Como prova dos noves, basta efetuar o seguinte cálculo:

SELECT convert (char(10),
                dateadd (day, 
                         ticks, 
                         convert (datetime2, '1/1/0001', 103)),
                103) as [Prova dos 9];

Horário. Com relação ao horário, o número de bytes é variável de acordo com a precisão da coluna, tendo de 3 a 5 bytes para armazenar o horário.

p028_quadro 6

No quadro 6, montado a partir do resultado do códigos #4 e #5, a precisão utilizada foi de 7 algarismos, o que demanda 5 bytes para armazenar o horário. Da mesma forma que para a data, o byte menos significativo (low order) é o primeiro à esquerda.

O significado do valor numérico obtido como ticks depende da precisão, pois cada unidade acrescentada ao valor é um time tick, cujo valor é o descrito na seguinte tabela:

p028_quadro 7

onde ms é a sigla de milissegundo; µs de microssegundo; e ns de nanossegundo.

O código #5, ao final deste artigo, calcula os ticks de horário considerando a precisão definida na declaração da variável com o tipo de dados datetime2. A partir do valor da precisão obtém-se n bytes, estes são girados, convertidos para binário e a seguir para bigint. O valor obtido é o número de ticks referente ao horário. É claro que no dia a dia esses cálculos não são necessários, mas eles servem para demonstrar a forma como o SQL Server armazena data e hora para o tipo de dados datetime2.

Exercício de fixação. Considerando-se o seguinte valor, 00|78|70|33|5C|BF|40|0B, e que ele representa o conteúdo de uma coluna do tipo datetime2, qual é a data e horário?

2.3. datetimeoffset

O tipo de dados datetimeoffset permite armazenar data, horário e também o registro de fuso horário. Possui alta precisão (100 nanossegundos), permitindo definir até 7 casas fracionárias de segundo:

Faixa de valores:
01/01/0001 00:00:00.0000000 -14:00 
  a 31/12/9999 23:59:59.9999999 +14:00

Da mesma forma que o tipo de dados datetime2, a precisão da parte fracionária do tipo de dados datetimeoffset pode ser ajustada, indo de 0 a 7 algarismos. Por causa disso que o espaço físico ocupado varia de 8 a 10 bytes, dependendo justamente da precisão escolhida na fração de segundos: 0 a 2 algarismos: 8 bytes; 3 ou 4 algarismos: 9 bytes; e 5 a 7 algarismos: 10 bytes.

p028_bytes datetimeoffset

Data. Para armazenar a data são utilizados 3 bytes, do terceiro ao quinto da direita para a esquerda (conforme figura acima), sendo que o valor 0 (zero) representa a data “1/1/0001”; o valor 1 (um) representa a data “2/1/0001”; e assim em diante:

p028_quadro 8

No quadro 8, montado a partir do resultado do código #6, os bytes estão separados pelo caractere “|” e os bytes que armazenam a data estão sublinhados. Quando um valor do tipo datetimeoffset é convertido para binary, um byte extra é acrescentado à esquerda do conjunto original, para informar a precisão. É por este motivo que na coluna hexadecimal há 11 bytes (ou 22 algarismos base 16).

Ao analisar o conteúdo da coluna hexadecimal, bytes de data, observa-se que o byte menos significativo (low order) é o primeiro à esquerda, o que significa que utiliza little endian. Para converter de little endian para big endian é necessário obter os 3 bytes, inverter a ordem deles e então obter o valor de tick de dias. O mesmo processo documentado para o tipo de dados datetime2.

Para a data de 1/1/0002 obtém-se o valor 365 na coluna Ticks, que é o número de dias a somar à data base (1/1/0001). Como prova dos noves, basta efetuar o seguinte cálculo:

SELECT convert (char(10),
                dateadd (day, 
                         ticks, 
                         convert (datetimeoffset, '1/1/0001', 103)),
                103) as [Prova dos 9];

Horário. Com relação ao horário, o número de bytes é variável de acordo com a precisão da coluna, tendo de 3 a 5 bytes para armazenar o horário. Na documentação do tipo de dados datetimeoffset consta que “The data is stored in the database and processed, compared, sorted, and indexed in the server as in UTC. The time zone offset will be preserved in the database for retrieval. Ou seja, o horário é convertido para o fuso UTC antes de ser armazenado.

Considerando-se o fuso horário UTC (ou seja, 00:00), temos o seguinte quadro para 4 horários diferentes:

p028_quadro 9

No quadro 9, montado a partir do resultado do código #7, a precisão utilizada foi de 7 algarismos, o que demanda 5 bytes para armazenar o horário. Da mesma forma que para a data, o byte menos significativo (low order) é o primeiro à esquerda.

O significado do valor numérico obtido como ticks depende da precisão, pois cada unidade acrescentada ao valor é um time tick, conforme quadro 7 do item de datetime2.

Fuso horário. caso se informe o fuso horário diferente de 0, os 2 bytes à direita são utilizados para registrar o fuso horário, em minutos. Por exemplo, se o fuso horário é +01:00 então é armazenado o valor 60.

p028_quadro 10

Observe que o mesmo valor para data e horário, dia “1/1/0001” e horário à 1 h, possui representações bem diferentes dependendo do fuso horário. Isto ocorre porque o horário é sempre armazenado no fuso horário UTC, independente de qual seja o fuso horário original.

Da mesma forma que a parte de hora e de horário, para o valor do fuso horário é armazenado com a representação little endian, isto é, o byte mais significativo é o último. Então, 0x3C00 é transformado em 0x003C e, ao converter da base 16 para a base 10 temos o valor 60.

Para restaurar o horário original, percebe-se que o valor da parte de horas deve ser acrescentado (ou subtraído) do valor de fuso horário.

Exercício de fixação. Considerando-se o seguinte valor, 00|B0|BD|58|75|BF|40|0B|4C|FF, e que ele representa o conteúdo de uma coluna do tipo datetimeoffset, qual é a data, horário e fuso horário?

2.4. time

O tipo de dados time permite armazenar horário, sem registro de fuso. Possui alta precisão (100 nanossegundos), permitindo definir até 7 casas fracionárias de segundo:

Faixa de valores:
00:00:00.0000000 a 23:59:59.9999999

Internamente é armazenado no banco de dados como um valor que ocupa de 3 a 5 bytes. Este tipo de dados foi implementado na versão 2008 do SQL Server. Da mesma forma que o tipo de dados datetime2, a precisão da parte fracionária do tipo de dados time pode ser ajustada, indo de 0 a 7 algarismos. Por causa disso que o espaço físico ocupado varia de 3 a 5 bytes, dependendo justamente da precisão escolhida: 0 a 2 algarismos: 3 bytes; 3 ou 4 algarismos: 4 bytes; e 5 a 7 algarismos: 5 bytes.

p028_bytes time

As informações coletadas para o tipo de dados time são as mesmas da parte de horário do tipo de dados datetime2, pois ambas utilizam o mesmo padrão. Do resultado da execução do código #8 (ao final deste artigo) obteve-se o seguinte quadro:

p028_quadro 11

No quadro acima os bytes estão separados pelo caractere “|” e os bytes que armazenam o horário estão sublinhados. Quando um valor do tipo time é convertido para binary, um byte extra é acrescentado à esquerda do conjunto original, para informar a precisão. É por este motivo que na coluna hexadecimal há 6 bytes (ou 12 algarismos base 16).

A precisão utilizada foi de 7 algarismos, o que demanda 5 bytes para armazenar o horário. Da mesma forma que para a data, o byte menos significativo (low order) é o primeiro à esquerda.

Variação na precisão. Nos exemplos anteriores a precisão foi mantida em 7 para facilitar comparações. Mas o que ocorre quando se reduz a precisão? Eis quadro com demonstração de como é o armazenamento de horário no tipo de dados time quando a precisão varia de 7 a 1, mantendo sempre 1 como último algarismo no horário:

p028_quadro 12

Embora nos valores de horário do quadro 12 o valor de tick seja sempre o mesmo, 1, o significado do valor numérico obtido como ticks depende da precisão (o primeiro byte, no caso de conversão de time para binary), pois cada unidade acrescentada ao valor é um time tick.

Exercício de fixação. Considerando-se o seguinte valor, 00|78|70|33|5C, e que ele representa o conteúdo de uma coluna do tipo time, qual é o horário?

2.5. date

O tipo de dados date permite armazenar datas, sem horário, iniciando no primeiro dia do ano 1 e indo até o último dia do ano de 9999:

Faixa de valores:
01/01/0001 a 31/12/9999

Internamente é armazenado no banco de dados como valor inteiro e ocupa 3 bytes. Este tipo de dados foi implementado na versão 2008 do SQL Server.

p028_bytes date

Os 3 bytes (numerados de 1 a 3 na figura acima) armazenam o valor numérico da data, sendo que o valor 0 (zero) representa a data “1/1/0001”; o valor 1 (um) representa a data “2/1/0001”; e assim em diante:

p028_quadro 13

No quadro 13, montado a partir do resultado do código #10, os bytes estão separados pelo caractere “|”. Ao analisar o conteúdo da coluna hexadecimal observa-se que o byte menos significativo (low order) é o primeiro à esquerda, o que significa que utiliza little endian. Para converter de little endian para big endian é necessário obter os 3 bytes, inverter a ordem deles e então obter o valor de tick de dias.

Para a data de 1/1/0002 obtém-se o valor 365 na coluna Ticks, que é o número de dias a somar à data base (1/1/0001). Como prova dos noves, basta efetuar o seguinte cálculo:

SELECT convert (char(10),
                dateadd (day, 
                         ticks, 
                         convert (date, '1/1/0001', 103)),
                103) as [Prova dos 9];

Exercício de fixação. Considerando-se o seguinte valor, 5B|95|0A, e que ele representa o conteúdo de uma coluna do tipo date, qual é a data?

3. Observações

Com o exposto ao longo deste artigo podemos então confirmar que a forma como o SQL Server armazena data & horário não é “yyyy-mm-dd hh:mm:ss.nnn” e muito menos depende do idioma do sistema operacional ou do idioma do SQL Server instalado ou ainda de qualquer configuração regional da sessão ou do Windows. 😉

Códigos SQL. Vários quadros do artigo foram montados utilizando resultados de códigos SQL especialmente construídos para analisar o conteúdo de colunas dos vários tipos de dados. Geralmente nos artigos que escrevo vou dispondo os códigos SQL ao longo do texto, de modo que o leitor possa acompanhar o desenvolvimento do texto. Entretanto, neste artigo optei por reuni-los em um pacote zip, contendo arquivos individuais para cada código SQL.

Clique em Armazenamento de datas e horas.sql.zip para receber o conjunto de arquivos texto contendo os códigos SQL deste artigo.

Gostou deste artigo? Então clica no botão CURTIR aí embaixo.

Publicidade

Uma consideração sobre “Como datas & horas são armazenadas no SQL Server?”

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair /  Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair /  Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair /  Alterar )

Conectando a %s

Este site utiliza o Akismet para reduzir spam. Saiba como seus dados em comentários são processados.

%d blogueiros gostam disto: