SQL Server Datetime vs. Datetime2

Il existe de nombreux formats d’heure et de date dans SQL Server avec différentes plages, précisions, tailles de stockage et précisions en secondes fractionnaires définies par l’utilisateur.

Voici un bref aperçu:

Nous voulons nous concentrer sur la comparaison du format datetime et datetime2. Dans mon entreprise actuelle, je rencontre beaucoup de tables héritées qui utilisent datetime. Datetime2 a été introduit pour la première fois dans SQL Server 2008. Cependant, je pense que certains développeurs ne connaissent tout simplement pas les avantages et les inconvénients de datetime2.

Datetime

Discutons d’abord un peu de datetime. Comme vous pouvez le voir ci-dessus, il a besoin de 8 octets de stockage et a une plage allant de 1753-01-01 à 9999-12-31. Notamment, il a une courte portée vers l’arrière. En effet, la Grande-Bretagne est passée du calendrier julien au calendrier grégorien en 1752 en sautant quelques jours. Pour être plus précis, le 2 septembre 1752 fut suivi du 14 septembre 1752. Parce qu’une date avant 1753 serait ambiguë, le type datetime n’est pas valide avant 1753. Une autre propriété tout à fait remarquable du type de données datetime est la précision de 0,00333 seconde, soit en fait 1/300 de seconde.

Cela semble un peu étrange. Nous n’avons pas de précision en millisecondes avec datetime. OK, mais pourquoi? Analysons le type de données datetime en profondeur. Dans un datetime, nous utilisons 4 octets pour la date et 4 octets pour l’heure. Comment ça marche exactement ? Jetons un coup d’œil.

DECLARE @test DATETIME = '2015-11-29 10:00:00.000';SELECT CAST(@test as varbinary(8))> 0x0000A55F00A4CB80

Donc 0x0000A55F00A4CB80 est hexadécimal. Séparons les 8 octets en deux morceaux. D’abord la date. 0x0000A55F représente la date. En décimal, c’est 42335. C’est le nombre de jours écoulés depuis le 1900-01-01. Preuve:

SELECT DATEADD(DD,42335,'1900-01-01')> 2015-11-29

Maintenant, pour le moment, nous avons les 4 derniers octets 0xA4CB80 traduits en décimal, c’est 10800000. Cela signifie 10800000 tiques à partir de minuit. Rappelez-vous que j’ai dit que la précision est de 1/300 de seconde? Cela est dû au fait que datetime stocke l’heure en ticks. Donc 10800000 tiques depuis minuit signifie 10800000 fois 1/300 de seconde. Calculons un peu.

Nous avons donc exactement 10 heures à partir de minuit et cela se traduit parfaitement à 10:00:00. Combiné avec la date que nous avons 2015-11-29 10:00:00.

Rappelez-vous que datetime utilise toujours 8 octets de stockage et gardez également à l’esprit que les quatre premiers octets représentant la date peuvent être négatifs (2complement) car la date peut être antérieure à 1900. Par exemple, dans 1890-11-29, vous obtenez les 4 premiers octets en tant que 0xFFFFF308, ce qui se traduit par un complément 2 bits 32 à -3320. Et 3320 soustraits de 1900-01-01 est exactement 1890-11-29.

datetime2

Tous les types de données de date et d’heure introduits avec SQL Server 2008 ont un type de stockage complètement nouveau que nous examinerons maintenant. Le type de données datetime2 utilise 6 à 8 octets en fonction de la précision de la miliseconde.

DECLARE @test DATETIME2(3) = '2015-11-29 10:00:00.000';SELECT CAST(@test as varbinary(8))> 0x0300512502BA3A0B

Cette fois, cela devient un peu plus compliqué. Dans tous les nouveaux types de données datetime, les trois DERNIERS octets représentent la date. Cela est dû à un changement d’ordre des octets. Ainsi, datetime est stocké en tant que petit endian, ce qui signifie que l’octet le plus significatif est situé le plus à gauche tandis que dans big endian, l’octet le plus significatif est stocké à la position la plus à droite.

Cela signifie que lorsque nous prenons 0x0300512502BA3A0B, la date n’est pas 0xBA3A0B mais 0x0B3ABA, car un octet est 2 chiffres hexadécimaux.

Encore une fois avec le calcul: 0x0B3ABA représente la décimale 735930. C’est exactement la date que nous voulions:

SELECT DATEADD(DD,735930,CAST('0001-01-01' as date))> 2015-11-29

Maintenant que les octets sont convertis, nous pouvons simplement prendre les derniers octets de la représentation little endian qui est 0x0225510003. Gardez à l’esprit que le tout dernier octet de little endian (c’est-à-dire le premier octet du big endian d’origine) est la précision indiquée. Comme vous pouvez le voir, nous avons défini datetime2(3), ce qui signifie que notre tout dernier octet est 0x03.

Faire le calcul: 0x02255100 est en décimal 36000000. Puisque nous avons utilisé la précision 3, ce qui signifie une précision à 3 chiffres, nous calculons d’abord les secondes en divisant notre nombre par 10 à la puissance de précision qui est dans notre cas 103.

Cela se traduit également parfaitement par 10 heures 0 minutes 0 secondes comme indiqué.

datetime vs datetime2

Enfin une comparaison simple et simple entre ces deux types de données.

date et heure date et heure 2
précision impaire précise maximale de 1/300 précision de 100 nanosecondes
précision définie par l’utilisateur non oui allant de 0 à 7
espace de stockage toujours 8 octets 6 – 8 octets selon la précision
utilisable avec l’opérateur + ou – oui non, utilisez datediff, dateadd etc.
Compatible avec la norme SQL non oui

Donc, dans l’ensemble, vous voyez que datetime utilise potentiellement plus de stockage, a une précision inférieure et étrange, a une plage inférieure et n’est pas compatible avec la norme SQL, ce qui fait que votre code se comporte différemment sur différents SGBD. Donc, si votre application prend en charge date, datetime2 et datetimeoffset, je conseille fortement d’utiliser les nouveaux types de données datetime car ils n’ont pratiquement aucun inconvénient.

Merci pour votre temps. Pour des articles plus intéressants, visitez http://www.dirtyread.de/

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée.