SQL Server Datetime vs. Datetime2

det finns många tid och datumformat i SQL Server med olika intervall, noggrannhet, lagringsstorlekar och användardefinierade fraktionerad andra precisioner.

Nedan följer en kort översikt:

vi vill fokusera på jämförelsen av datetime och datetime2-format. I mitt nuvarande företag stöter jag på många äldre tabeller som använder datetime. Datetime2 introducerades först i SQL Server 2008. Men jag tror att vissa utvecklare helt enkelt inte vet om fördelarna och nackdelarna med datetime2.

Datetime

Låt oss först diskutera datetime lite. Som du kan se ovan behöver den 8 byte lagring och har ett intervall från 1753-01-01 till 9999-12-31. I synnerhet har den en kort räckvidd bakåt. Detta beror på att Storbritannien flyttade från Julian till gregorianska kalendern 1752 genom att hoppa över några dagar. För att vara mer exakt följdes den 2 September 1752 av den 14 September 1752. Eftersom ett datum före 1753 skulle vara tvetydigt är datetime-typen inte giltig före 1753. En annan ganska märkbar egenskap hos datetime-datatypen är noggrannheten på 0,00333 sekunder som faktiskt är 1/300 av en sekund.

detta verkar lite konstigt. Vi har inte millisekund noggrannhet med datetime. OK, men varför? Låt oss analysera datetime datatyp på djupet. I en datetime använder vi 4 byte för datum och 4 byte för tid. Hur fungerar det exakt? Låt oss ta en titt.

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

0x0000A55F00A4CB80 är hexadecimal. Låt oss separera de 8 byte i två delar. Först datumet. 0x0000A55F representerar datumet. I decimal är det 42335. Det är antalet dagar som gått sedan 1900-01-01. Bevis:

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

nu för tiden har vi de sista 4 byte 0xA4CB80 översatt till decimal är det 10800000. Det betyder 10800000 fästingar från midnatt på. Kom ihåg att jag sa noggrannheten är 1/300 av en sekund? Det beror på det faktum att datetime lagrar tiden i fästingar. Så 10800000 fästingar sedan midnatt betyder 10800000 gånger 1/300 av en sekund. Låt oss beräkna lite.

så vi har exakt 10 timmar från midnatt och det översätter perfekt till 10:00:00. Kombinerat med det datum vi har 2015-11-29 10:00:00.

kom ihåg att datetime alltid använder 8 byte lagring och kom också ihåg att de första fyra byte som representerar datumet kan vara negativa (2komplement) eftersom datumet kan vara före 1900. Till exempel, i 1890-11-29 får du de första 4 byte som 0xFFFFF308, som översätts som 32-bitars 2-komplement till -3320. Och 3320 substrat från 1900-01-01 är exakt 1890-11-29.

datetime2

alla datum-och tidsdatatyper som introduceras med SQL Server 2008 har en helt ny lagringstyp som vi kommer att undersöka nu. Datatypen datetime2 använder 6 till 8 byte beroende på milisekundprecisionen.

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

den här gången blir det lite mer komplicerat. I alla nya datetime-datatyper representerar de tre sista byte datumet. Det beror på en förändring av byteordning. Så datetime lagras som little Endian,vilket betyder att den viktigaste byten är längst till vänster medan i big endian lagras den viktigaste byten längst till höger.

det betyder att när vi tar 0x0300512502BA3A0B är datumet inte 0xBA3A0B men 0x0B3ABA, eftersom en byte är 2 hexadecimala siffror.

återigen med matematiken: 0x0b3aba representerar decimal 735930. Det här är exakt det datum vi ville ha:

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

nu när byte konverteras kan vi bara ta de sista byte av little endian-representation som är 0x0225510003. Tänk på att den allra sista byten i little Endian (det är den första byten i original big endian) är den angivna precisionen. Som du kan se definierade vi datetime2 (3) Det betyder att vår allra sista byte är 0x03.

gör matten: 0x02255100 är i decimal 36000000. Eftersom vi använde precision 3, vilket betyder 3-siffrig precision, beräknar vi sekunderna först genom att dividera vårt nummer med 10 till precisionskraften som i vårt fall är 103.

detta översätter också perfekt till 10 timmar 0 minuter 0 sekunder precis som sagt.

datetime vs datetime2

slutligen en enkel och vanlig jämförelse mellan dessa två datatyper.

datumtid datumtid2
max exakt udda precision av 1/300 100 nanosekund precision
användardefinierad precision Nej ja från 0 till 7
lagringsutrymme alltid 8 byte 6-8 byte beroende på precision
användbar med + eller-operatör ja Nej, använd datediff, dateadd etc.
SQL Standard kompatibel Nej ja

så totalt sett ser du att datetime använder potentiellt mer lagring, har en lägre och udda precision, har lägre intervall och är inte kompatibel med SQL-standarden, vilket gör att din kod beter sig annorlunda på olika DBMS. Så om din ansökan stöder datum, datetime2 och datetimeoffset jag starkt råd om att använda de nya datetime datatyper eftersom de har knappt någon nackdel.

Tack för din tid. För mer intressanta artiklar besök http://www.dirtyread.de/

Lämna ett svar

Din e-postadress kommer inte publiceras.