SQL Server Datetime vs.Datetime2

w SQL Server istnieje wiele formatów czasu i Daty z różnymi zakresami, dokładnościami, rozmiarami pamięci i zdefiniowanymi przez użytkownika ułamkowymi dokładnościami sekundowymi.

poniżej znajduje się krótki przegląd:

chcemy skupić się na porównaniu formatu datetime i datetime2. W mojej obecnej firmie spotykam wiele starszych tabel, które używają datetime. Datetime2 został po raz pierwszy wprowadzony w SQL Server 2008. Myślę jednak, że niektórzy programiści po prostu nie wiedzą o zaletach i wadach datetime2.

Datetime

najpierw omówmy trochę datetime. Jak widać powyżej, potrzebuje 8 bajtów pamięci i ma zakres od 1753-01-01 do 9999-12-31. Warto zauważyć, że ma krótki zasięg do tyłu. Dzieje się tak dlatego, że Wielka Brytania przesunęła się z kalendarza juliańskiego na gregoriański w 1752 roku, pomijając kilka dni. Dokładniej mówiąc, 2 września 1752 roku nastąpiło 14 września 1752 roku. Ponieważ Data przed 1753 byłaby niejednoznaczna, Typ datetime nie jest ważny przed 1753. Inną dość zauważalną właściwością typu datetime jest dokładność 0,00333 sekundy, czyli w rzeczywistości 1/300 sekundy.

to wydaje się trochę dziwne. Nie mamy milisekundowej dokładności z datetime. OK, ale dlaczego? Przeanalizujmy dogłębnie typ danych datetime. W DateTime używamy 4 bajtów dla daty i 4 bajtów dla czasu. Jak to dokładnie działa? Zobaczmy.

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

więc 0x0000A55F00A4CB80 jest szesnastkowy. Podzielmy 8 bajtów na dwie części. Najpierw randka. 0x0000A55F reprezentuje datę. W układzie dziesiętnym jest to 42335. Tyle dni minęło od 1900-01-01. Dowód:

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

teraz mamy ostatnie 4 bajty 0xA4CB80 przetłumaczone na dziesiętne to jest 10800000. To oznacza 10800000 kleszczy od północy. Pamiętasz, jak mówiłem, że dokładność wynosi 1/300 sekundy? Wynika to z faktu, że datetime przechowuje czas w wskazach. 10800000 kleszczy od północy oznacza 10800000 razy 1/300 sekundy. Obliczmy trochę.

więc mamy dokładnie 10 godzin od północy i to doskonale przekłada się na 10:00:00. W połączeniu z datą mamy 2015-11-29 10:00: 00.

pamiętaj, że datetime używa zawsze 8 bajtów pamięci, a także pamiętaj, że pierwsze cztery bajty reprezentujące datę mogą być ujemne (2), ponieważ data może być przed 1900. Na przykład w 1890-11-29 otrzymujesz pierwsze 4 bajty jako 0xFFFFF308, co tłumaczy się jako 32-bitowe 2-dopełnienie na -3320. A 3320 odjęte od 1900-01-01 to dokładnie 1890-11-29.

datetime2

wszystkie typy danych daty i godziny wprowadzone w SQL Server 2008 mają zupełnie nowy typ pamięci, który teraz zbadamy. Typ danych datetime2 wykorzystuje 6 do 8 bajtów w zależności od precyzji milisekundy.

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

tym razem robi się to nieco bardziej skomplikowane. We wszystkich nowych typach datetime ostatnie trzy bajty reprezentują datę. Wynika to ze zmiany kolejności bajtów. Tak więc datetime jest zapisywany jako little endian, co oznacza, że najważniejszy bajt znajduje się na lewym, podczas gdy w big endian najważniejszy bajt jest zapisywany na prawym.

oznacza to, że gdy weźmiemy 0x0300512502BA3A0B, data nie jest 0xBA3A0B, ale 0x0b3aba, ponieważ jeden bajt to 2 cyfry szesnastkowe.

ponownie z matematyki: 0x0b3aba reprezentuje dziesiętne 735930. To jest dokładnie data, o którą chcieliśmy:

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

teraz, gdy bajty są konwertowane, możemy po prostu wziąć Ostatnie bajty małej reprezentacji endiana, czyli 0x0225510003. Należy pamiętać, że ostatni bajt w little endian (jest to pierwszy bajt w oryginalnym big endian) jest precyzją podaną. Jak widać, zdefiniowaliśmy datetime2 (3), co oznacza, że nasz ostatni bajt to 0x03.

Obliczanie: 0x02255100 jest liczbą dziesiętną 36000000. Ponieważ użyliśmy precyzji 3, co oznacza 3-cyfrową precyzję, najpierw obliczamy sekundy dzieląc naszą liczbę przez 10 do potęgi precyzji, która jest w naszym przypadku 103.

przekłada się to również idealnie na 10 godzin 0 minut 0 sekund, tak jak podano.

datetime vs datetime2

wreszcie proste i proste porównanie tych dwóch typów danych.

datetime datetime2
maksymalna dokładność nieparzysta dokładność 1/300 100 nanosekundowa precyzja
precyzja zdefiniowana przez użytkownika nie tak od 0 do 7
przestrzeń dyskowa zawsze 8 bajtów 6-8 bajtów w zależności od precyzji
możliwość użycia z operatorem + lub – tak Nie, użyj datediff, dateadd itp.
zgodny ze standardem SQL nie tak

tak więc ogólnie widzisz, że datetime zużywa potencjalnie więcej pamięci, ma niższą i dziwną precyzję, ma niższy zakres i nie jest kompatybilny ze standardem SQL, co sprawia, że Twój kod zachowuje się inaczej na różnych DBMS. Jeśli więc Twoja aplikacja obsługuje date, datetime2 i datetimeoffset, zdecydowanie radzę używać nowych typów danych datetime, ponieważ mają one prawie wadę.

dziękuję za poświęcony czas. Więcej ciekawych artykułów na stronie http://www.dirtyread.de/

Dodaj komentarz

Twój adres e-mail nie zostanie opublikowany.