SQL Server Datetime vs. Datetime2

In SQL Server gibt es viele Zeit- und Datumsformate mit unterschiedlichen Bereichen, Genauigkeiten, Speichergrößen und benutzerdefinierten Sekundenbruchteilen.

Nachfolgend ein kurzer Überblick:

Wir wollen uns auf den Vergleich von datetime und datetime2 Format konzentrieren. In meiner aktuellen Firma stoße ich auf viele Legacy-Tabellen, die datetime verwenden. Datetime2 wurde erstmals in SQL Server 2008 eingeführt. Ich denke jedoch, dass einige Entwickler die Vor- und Nachteile von datetime2 einfach nicht kennen.

Datetime

Lassen Sie uns zuerst datetime ein wenig diskutieren. Wie Sie oben sehen können, benötigt es 8 Byte Speicher und hat einen Bereich von 1753-01-01 bis 9999-12-31. Vor allem hat es eine kurze Reichweite rückwärts. Dies liegt daran, dass Großbritannien 1752 vom julianischen zum gregorianischen Kalender wechselte, indem es einige Tage übersprang. Genauer gesagt, dem 2. September 1752 folgte der 14.September 1752. Da ein Datum vor 1753 mehrdeutig wäre, ist der datetime-Typ vor 1753 nicht gültig. Eine weitere auffällige Eigenschaft des Datentyps datetime ist die Genauigkeit von 0,00333 Sekunden, was tatsächlich 1/300 Sekunde entspricht.

Dies scheint ein wenig seltsam. Wir haben keine Millisekundengenauigkeit mit datetime. OK, aber warum? Lassen Sie uns den Datentyp datetime eingehend analysieren. In einer datetime verwenden wir 4 Bytes für date und 4 bytes für time . Wie funktioniert das genau? Schauen wir mal.

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

Also ist 0x0000A55F00A4CB80 hexadezimal. Trennen wir die 8 Bytes in zwei Teile. Zuerst das Datum. 0x0000A55F repräsentiert das Datum. In dezimal ist es 42335. Das ist die Anzahl der Tage, die seit dem 01.01.1900 vergangen sind. Beweis:

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

Jetzt für die Zeit haben wir die letzten 4 Bytes 0xA4CB80 in dezimal übersetzt ist es 10800000. Das bedeutet 10800000 Ticks ab Mitternacht. Denken Sie daran, ich sagte, die Genauigkeit ist 1/300 Sekunde? Dies liegt an der Tatsache, dass datetime die Zeit in Ticks speichert. 10800000 Ticks seit Mitternacht bedeuten also 10800000 mal 1/300 Sekunde. Lassen Sie uns ein wenig berechnen.

Wir haben also genau 10 Stunden ab Mitternacht und das bedeutet perfekt 10:00:00 Uhr. Kombiniert mit dem Datum haben wir 2015-11-29 10:00:00.

Denken Sie daran, dass datetime immer 8 Byte Speicher verwendet und dass die ersten vier Bytes, die das Datum darstellen, negativ sein können (2complement), da das Datum vor 1900 liegen kann. Zum Beispiel erhalten Sie in 1890-11-29 die ersten 4 Bytes als 0xFFFFF308, was als 32-Bit-2-Komplement zu -3320 übersetzt wird. Und 3320 subtrahiert von 1900-01-01 ist genau 1890-11-29.

datetime2

Alle Datums- und Uhrzeitdatentypen, die mit SQL Server 2008 eingeführt wurden, haben einen völlig neuen Speichertyp, den wir jetzt untersuchen werden. Der Datentyp datetime2 verwendet je nach Millisekundengenauigkeit 6 bis 8 Byte.

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

Dieses Mal wird es etwas komplizierter. In allen neuen Datetime-Datentypen stellen die LETZTEN drei Bytes das Datum dar. Dies liegt an einer Änderung der Bytereihenfolge. Datetime wird also als Little Endian gespeichert, dh das höchstwertige Byte befindet sich ganz links, während in Big Endian das höchstwertige Byte ganz rechts gespeichert ist.

Das bedeutet, wenn wir 0x0300512502BA3A0B nehmen, ist das Datum nicht 0xBA3A0B, sondern 0x0B3ABA, da ein Byte 2 hexadezimale Ziffern ist.

Wieder mit der Mathematik: 0x0B3ABA repräsentiert die Dezimalzahl 735930. Dies ist genau das Datum, das wir wollten:

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

Jetzt, da die Bytes konvertiert sind, können wir nur die letzten Bytes der Little-Endian-Darstellung nehmen, die 0x0225510003 ist. Beachten Sie, dass das allerletzte Byte in Little Endian (dh das erste Byte im ursprünglichen Big Endian) die angegebene Genauigkeit ist. Wie Sie sehen können, haben wir datetime2(3) definiert, was bedeutet, dass unser allerletztes Byte 0x03 ist.

Rechnen: 0x02255100 ist dezimal 36000000. Da wir Präzision 3 verwendet haben, was 3-stellige Genauigkeit bedeutet, berechnen wir zuerst die Sekunden, indem wir unsere Zahl durch 10 auf die Potenz der Genauigkeit teilen, die in unserem Fall 103 ist.

Dies entspricht auch perfekt 10 Stunden 0 Minuten 0 Sekunden, genau wie angegeben.

datetime vs datetime2

Endlich ein einfacher und einfacher Vergleich zwischen diesen beiden Datentypen.

datetime datetime2
max präzise odd präzision von 1/300 100 nanosekunden präzision
benutzerdefinierte Genauigkeit nein ja von 0 bis 7
speicherplatz immer 8 Byte 6 – 8 Byte je nach Genauigkeit
verwendbar mit + oder – Operator ja nein, verwenden Sie datediff, dateadd usw.
SQL Standard kompatibel nein ja

Insgesamt sehen Sie also, dass Datetime potenziell mehr Speicher verbraucht, eine geringere und ungerade Genauigkeit aufweist, einen niedrigeren Bereich aufweist und nicht mit dem SQL-Standard kompatibel ist, wodurch sich Ihr Code auf verschiedenen DBMS unterschiedlich verhält. Wenn Ihre Anwendung also date, datetime2 und datetimeoffset unterstützt, empfehle ich dringend, die neuen datetime Datentypen zu verwenden, da sie kaum Nachteile haben.

Vielen Dank für Ihre Zeit. Weitere interessante Artikel finden Sie unter http://www.dirtyread.de/

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht.