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

Datetime2 x datetime

Como você decide a forma de armazenar data no banco de dados: smalldatetime, datetime ou char(8)? E o horário? Seja como for, a Microsoft lançou novos tipos de dados para armazenamento de data e hora. Afinal, p027_abertura

Pois é…

1. Introdução

Para armazenar data e horário até a versão 2005 do SQL Server somente existiam os tipos de dados SMALLDATETIME e DATETIME. A partir da versão 2008 surgiram novos tipos de dados para data e/ou hora, inclusive para armazenar somente data (tipo de dados DATE) ou somente o horário (tipo de dados TIME). Além desses dois surgiram também os tipos de dados DATETIME2 e DATETIMEOFFSET.

O tipo de dados que mais percebo como utilizado em banco de dados SQL Server para data & horário é o DATETIME. Não sei se é por causa de bancos de dados com estruturas antigas ou se o pessoal que cria os bancos de dados que é antigo…

Dos novos tipos de dados é curiosa a relação entre os tipos de dados DATETIME e DATETIME2. A princípio não faz sentido existir o tipo de dados DATETIME2, pois existe o DATETIME que, aparentemente, tem a mesma função. Entretanto, há sim diferenças entre os dois tipos de dados e decifrá-las é o objetivo deste artigo.

2. Características de datetime2 e datetime

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.

p027_faixa valores datetime2

Internamente é armazenado no banco de dados como um valor que ocupa de 6 a 8 bytes, dependendo da quantidade de algarismos da fração de segundos: 0 a 2: 6 bytes; 3 ou 4: 7 bytes; e 5 a 7: 8 bytes.

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:

p027_faixa valores datetime

Internamente é armazenado no banco de dados como um valor que ocupa 8 bytes, sendo que o valor de horário possui precisão de 1 / 300 de segundo, que na prática é algo próximo de 3 milissegundos. Observe que a parte fracionária de segundos está 997 e não 999.

Quadro comparativo: Uma rápida comparação entre

p027_quadro comparativo

3. Vantagens de datetime2

Analisando o quadro anterior percebem-se algumas vantagens para o tipo de dados datetime2:

  • faixa de anos mais ampla;
  • maior precisão na fração de segundos;
  • definição da quantidade de algarismos na fração de segundos (de 0 a 7);
  • possibilidade de ocupar 2 bytes a menos.

3.1. Faixa de anos mais ampla

A respeito de faixa de valores, do quadro comparativo anterior podemos extrair as seguintes informações:

p027_faixa anos

Ou seja, no tipo de dados datetime2 pode-se armazenar desde o ano 1 enquanto que no tipo de dados datetime somente a partir de 1753. Não há, por exemplo, como armazenar dados históricos do século XV com datetime.

3.2. Maior precisão na fração de segundos

A principal melhoria do tipo de dados datetime2 é na parte de fração de segundos. É interessante conhecer a diferença de comportamento entre os dois tipos de dados a respeito de armazenamento de valor.

-- código #1
declare @DT2 datetime2, @DT datetime;
set @DT2= convert (datetime2, '29/2/2020 20:21:33.7654321', 103);

set @DT= convert (datetime, '29/2/2020 20:21:33.765', 103);
SELECT format (@DT2, 'dd-MM-yyyy HH:mm:ss.fffffff') as [datetime2],
       format (@DT, 'dd-MM-yyyy HH:mm:ss.fff') as [datetime];

cujo resultado é

p027_codigo #1

Enquanto que no tipo de dados datetime2 o valor foi armazenado sem truncamento ou arredondamento, observe que no tipo de dados datetime ocorreu arredondamento de .765 para .767! O motivo é a forma como o horário é armazenado para tipo de dados datetime: ocupa 8 bytes, sendo que os 4 bytes iniciais armazenam a data e os 4 bytes finais armazenam o horário. Cada unidade de horário armazenada em coluna do tipo datetime é denominada de tick e é equivalente a 1 / 300 de segundo:

p027_tick datetime

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

3.3. Definição da quantidade de algarismos na fração de segundos

A sintaxe para declaração do tipo de dados datetime2 é

datetime2 [ (fractional seconds precision) ]

onde fractional seconds precision é a quantidade de algarismos que devem constar para a fração de segundos. Por exemplo, há como configurar que coluna com tipo de dados datetime2 utilize somente 3 casas fracionárias, à semelhança do tipo de dados datetime. Neste caso eis o comportamento de ambos:

-- código #2
declare @DT2 datetime2(3), @DT datetime;
set @DT2= convert (datetime2, '29/2/2020 20:21:33.765', 103);

set @DT= convert (datetime, '29/2/2020 20:21:33.765', 103);
SELECT format (@DT2, 'dd-MM-yyyy HH:mm:ss.fff') as [datetime2],
       format (@DT, 'dd-MM-yyyy HH:mm:ss.fff') as [datetime];

cujo resultado é

p027_codigo #2

Novamente se percebe a precisão do tipo de dados datetime2 sobre o tipo de dados datetime.

3.4. Redução no espaço físico em disco

Colunas com tipo de dados datetime2 ocupam de 6 a 8 bytes, dependendo da precisão configurada, isto é, da quantidade de algarismos na parte fracionária de segundos. Caso não seja necessário ter fração de segundos no horário pode-se declarar como datetime2(0), quando serão ocupados 6 bytes na tabela.

-- código #3
CREATE TABLE dbo.Info (
     DT2 datetime2,
     DT2_6 datetime2(6),
     DT2_5 datetime2(5),
     DT2_4 datetime2(4),
     DT2_3 datetime2(3),
     DT2_2 datetime2(2),
     DT2_1 datetime2(1),
     DT2_0 datetime2(0)
);

INSERT into dbo.Info
    (DT2, DT2_6, DT2_5, DT2_4, DT2_3, DT2_2, DT2_1, DT2_0)
values
    (convert (datetime2, '29/2/2020 20:21:33.1234567', 103),
     convert (datetime2, '29/2/2020 20:21:33.123456', 103),
     convert (datetime2, '29/2/2020 20:21:33.12345', 103),
     convert (datetime2, '29/2/2020 20:21:33.1234', 103),
     convert (datetime2, '29/2/2020 20:21:33.123', 103),
     convert (datetime2, '29/2/2020 20:21:33.12', 103),
     convert (datetime2, '29/2/2020 20:21:33.1', 103),
     convert (datetime2, '29/2/2020 20:21:33', 103));
go

SELECT col_length (N'dbo.Info', N'DT2') as [datetime2],
       col_length (N'dbo.Info', N'DT2_6') as [datetime2(6)],
       col_length (N'dbo.Info', N'DT2_5') as [datetime2(5)],
       col_length (N'dbo.Info', N'DT2_4') as [datetime2(4)],
       col_length (N'dbo.Info', N'DT2_3') as [datetime2(3)],
       col_length (N'dbo.Info', N'DT2_2') as [datetime2(2)],
       col_length (N'dbo.Info', N'DT2_1') as [datetime2(1)],
       col_length (N'dbo.Info', N'DT2_0') as [datetime2(0)];

O código SQL acima relaciona a ocupação, em bytes, para cada valor de fractional seconds precision.  O resultado é o seguinte:

p027_codigo #3

São 2 bytes a menos, redução de 25%. Atualmente discos são baratos, pode parecer que a princípio seria economia de palito. Entretanto, deve-se lembrar que quanto menor o tamanho médio de uma linha, maior a quantidade de linhas por página e, ao final, mais rapidamente os dados são transferidos do disco para a memória. E vice-versa.

4. Observações

No capítulo anterior foram definidas algumas das vantagens do tipo de dados datetime2 em comparação com o tipo de dados datetime. Mas, como sempre, cada caso é um caso. Ou seja, deve-se avaliar no contexto do banco de dados se alguma das vantagens relacionadas aplica-se localmente.

Se não necessita da maior precisão então pode reduzir o espaço físico ocupado ao declarar datetime2(2). São 6 bytes, o que significa redução de 25% no armazenamento em disco para a coluna. Se de imediato não percebeu nenhuma vantagem, ainda assim a sugestão é que utilize datetime2.

Data e hora atual. No caso de datetime2, para obter a data e hora atual deve-se utilizar a função SYSDATETIME(), que retorna a informação já no tipo de dados datetime2 e com precisão de 7 algarismos na parte fracionária. Por exemplo:

-- código #4
declare @Agora datetime2;
set @Agora= sysdatetime();
SELECT format (@Agora, 'dd-MM-yyyy HH:mm:ss.fffffff') as Agora;

Fato: ao optar por datetime2(3) tem-se o mesmo número de algarismos na parte fracionária de segundos: 3. E sem o arredondamento que ocorre no tipo de dados datetime, além de ocupar 1 byte a menos.

Mas atenção. A partir da versão 2016 do SQL Server (bem como no Azure SQL Database) o comportamento da conversão de tipos de dados datetime para datetime2 depende da configuração de nível de compatibilidade, conforme descrito no KB “SQL Server and Azure SQL Database improvements in handling some data types and uncommon operations“.

- código #5
USE testdb;
ALTER DATABASE testdb set COMPATIBILITY_LEVEL = 120;
go

declare @DT2 datetime2, @DT datetime;
set @DT= convert (datetime, '29/2/2020 20:21:33.765', 103);
set @DT2= cast (@DT as datetime2);

SELECT format (@DT, 'dd-MM-yyyy HH:mm:ss.fff') as [datetime],
       format (@DT2, 'dd-MM-yyyy HH:mm:ss.fffffff') as [datetime2],
       case when @DT = @DT2 then 'iguais'
            else 'diferentes' end as [Observação];
--

ALTER DATABASE testdb set COMPATIBILITY_LEVEL = 130;
go

declare @DT2 datetime2, @DT datetime;
set @DT= convert (datetime, '29/2/2020 20:21:33.765', 103);
set @DT2= cast (@DT as datetime2);

SELECT format (@DT, 'dd-MM-yyyy HH:mm:ss.fff') as [datetime],
       format (@DT2, 'dd-MM-yyyy HH:mm:ss.fffffff') as [datetime2],
       case when @DT = @DT2 then 'iguais'
            else 'diferentes' end as [Observação];

O resultado é o seguinte

p027_codigo #4

É preciso ficar atento a esta particularidade no caso de comparações de expressões do tipo de dados datetime com expressões do tipo de dados datetime2.

E então? O objetivo deste artigo foi o de reforçar a existência desse tipo de dados, de modo que os analistas de bancos de dados avaliem com atenção a utilização dele.

Enquete. O que te impede de utilizar datetime2 no lugar de datetime? Caso queira comentar a respeito, ao final deste artigo o espaço está aberto para opiniões.

Publicidade

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: