SQL Server Datetime vs. Datetime2

Esistono molti formati di data e ora in SQL Server con intervalli, precisioni, dimensioni di archiviazione e secondi frazionari definiti dall’utente.

Di seguito una breve panoramica:

Vogliamo concentrarci sul confronto tra il formato datetime e datetime2. Nella mia attuale azienda incontro molte tabelle legacy che utilizzano datetime. Datetime2 è stato introdotto per la prima volta in SQL Server 2008. Tuttavia penso che alcuni sviluppatori semplicemente non conoscono i vantaggi e gli svantaggi di datetime2.

Datetime

Parliamo prima di datetime un po’. Come puoi vedere sopra, ha bisogno di 8 byte di archiviazione e ha un intervallo da 1753-01-01 a 9999-12-31. In particolare, ha un breve raggio all’indietro. Questo perché la Gran Bretagna si trasferì dal calendario giuliano a quello gregoriano nel 1752 saltando alcuni giorni. Per essere più precisi, il 2 settembre 1752 fu seguito dal 14 settembre 1752. Poiché una data prima del 1753 sarebbe ambigua, il tipo datetime non è valido prima del 1753. Un’altra proprietà abbastanza evidente del datatype datetime è la precisione di 0,00333 secondi che è in realtà 1/300 di secondo.

Questo sembra un po ‘ strano. Non abbiamo una precisione di millisecondi con datetime. OK, ma perche’? Analizziamo il tipo di dati datetime in profondità. In un datetime usiamo 4 byte per la data e 4 byte per il tempo. Come funziona esattamente? Diamo un’occhiata.

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

Quindi 0x0000A55F00A4CB80 è esadecimale. Separiamo gli 8 byte in due pezzi. Prima l’appuntamento. 0x0000A55F rappresenta la data. In decimale è 42335. Questa è la quantità di giorni trascorsi dal 1900-01-01. Prova:

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

Ora per il momento abbiamo gli ultimi 4 byte 0xA4CB80 tradotti in decimale è 10800000. Ciò significa 10800000 zecche da mezzanotte in poi. Ricordi che ho detto che la precisione è 1/300 di secondo? Ciò è dovuto al fatto che datetime memorizza l’ora in tick. Quindi 10800000 zecche da mezzanotte significa 10800000 volte 1/300 di secondo. Calcoliamo un po’.

Quindi abbiamo esattamente 10 ore da mezzanotte e questo si traduce perfettamente alle 10: 00: 00. Combinato con la data che abbiamo 2015-11-29 10: 00: 00.

Ricorda che datetime utilizza sempre 8 byte di archiviazione e tieni presente che i primi quattro byte che rappresentano la data possono essere negativi (2complement) poiché la data può essere anteriore al 1900. Ad esempio, in 1890-11-29 si ottengono i primi 4 byte come 0xFFFFF308, che si traduce come complemento 2 a 32 bit a -3320. E 3320 sottratto da 1900-01-01 è esattamente 1890-11-29.

datetime2

Tutti i tipi di dati di data e ora introdotti con SQL Server 2008 hanno un tipo di archiviazione completamente nuovo che esamineremo ora. Il datatype datetime2 utilizza da 6 a 8 byte a seconda della precisione milisecondo.

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

Questa volta diventa un po ‘ più complicato. In tutti i nuovi tipi di dati datetime gli ultimi tre byte rappresentano la data. Ciò è dovuto a un cambiamento dell’ordine dei byte. Quindi datetime è memorizzato come little endian, il che significa che il byte più significativo è all’estrema sinistra mentre in big endian il byte più significativo è memorizzato nella posizione più a destra.

Ciò significa che quando prendiamo 0x0300512502BA3A0B la data non è 0xBA3A0B ma 0x0B3ABA, poiché un byte è 2 cifre esadecimali.

Di nuovo con la matematica: 0x0B3ABA rappresenta il decimale 735930. Questa è esattamente la data che volevamo:

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

Ora che i byte vengono convertiti, possiamo semplicemente prendere gli ultimi byte della rappresentazione di little endian che è 0x0225510003. Tieni presente che l’ultimo byte in little endian (che è il primo byte in big endian originale) è la precisione dichiarata. Come puoi vedere abbiamo definito datetime2 (3) che significa che il nostro ultimo byte è 0x03.

Facendo la matematica: 0x02255100 è in decimale 36000000. Poiché abbiamo usato precision 3, che significa precisione a 3 cifre, calcoliamo prima i secondi dividendo il nostro numero con 10 alla potenza di precisione che è nel nostro caso 103.

Questo si traduce perfettamente anche in 10 ore 0 minuti 0 secondi proprio come affermato.

datetime vs datetime2

Infine un confronto semplice e chiaro tra questi due tipi di dati.

datetime datetime2
max precise dispari precisione di 1/300 100 nanosecondi di precisione
definito dall’utente di precisione no sì, che vanno da 0 a 7
spazio di archiviazione sempre di 8 byte 6 – 8 byte a seconda di precisione
utilizzabile con + o – operatore no, utilizzare la funzione datediff, dateadd etc.
Compatibile con lo standard SQL no

Quindi, nel complesso, si vede che datetime utilizza potenzialmente più spazio di archiviazione, ha una precisione inferiore e dispari, ha un intervallo inferiore e non è compatibile con lo standard SQL, il che rende il codice diverso su DBMS diversi. Quindi, se la tua applicazione supporta date, datetime2 e datetimeoffset, consiglio vivamente di utilizzare i nuovi tipi di dati datetime poiché hanno a malapena uno svantaggio.

Grazie per il vostro tempo. Per articoli più interessanti visita http://www.dirtyread.de/

Lascia un commento

Il tuo indirizzo email non sarà pubblicato.