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 | sì | no, utilizzare la funzione datediff, dateadd etc. |
| Compatibile con lo standard SQL | no | sì |
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/