DATEADD e DATEPART con millisecondi producono risultati strani
Ho una tabella che contiene la data in unix tempo e un campo di millisecondi separato. Ora provo a creare una data dai due campi per il calcolo successivo (ad esempio filtrando su un intervallo di tempo). Dopo aver aggiunto i millisecondi alla data creata tramite ...
dateadd(S, [timestamp_s], '1970-01-01')
aggiungendo un altro DATEADD...
dateadd(MS, [timestamp_ms], dateadd(S, [timestamp_s], '1970-01-01')) eventdate
... e quindi visualizza la data in cui i millisecondi a volte sono meno di un millisecondo. Per curiosità ho quindi provato a estrarre i millisecondi solo per vedere cosa dà e si spegne di nuovo di 1 millisecondo.
Penso che abbia a che fare con la precisione in virgola mobile interna ma non vedo alcuna regola nei dati. A volte ogni operazione toglie 1 MS, a volte la prima sottrarrà 1 ma DATEPART aggiungerà di nuovo 1, ecc.
Poiché ciò potrebbe causare frustrazione ad alcuni utenti, vorrei capire il comportamento e idealmente trovare una soluzione al problema. Grazie in anticipo.
Risposte
Ciò è dovuto a una strana limitazione 1 nella precisione del datetimetipo di dati, come documentato :
Precisione | Arrotondato a incrementi di .000, .003 o .007 secondi
La soluzione sarebbe quella di utilizzare datetime2, che fornisce una migliore precisione, come puoi vedere in questa demo di dbfiddle :
Il tipo di ritorno di DATEADDè dinamico in base a ciò che ci invii. Quindi l'importante è assicurarsi di passare datetime2al passaggio in cui si aggiungono i millisecondi.
1 Randolph West ha una serie di blog su come vengono archiviati i tipi di dati di SQL Server, incluso uno su date e orari ( come SQL Server archivia i tipi di dati: date e orari ). Quel post contiene un commento utile dell'MVP di Data Platform Jeff Moden:
La parte temporale di DATETIME è in realtà un conteggio intero del numero di 1/300 di secondo trascorsi dalla mezzanotte. Ciò si traduce in una risoluzione di 3,3 millisecondi, ma quel livello di risoluzione non è possibile per il modo in cui i dati vengono archiviati, quindi 0,0 e 3,3 ms vengono arrotondati per difetto a 0 ms e 3 ms e 6,6 millisecondi vengono arrotondati per eccesso a 7 millisecondi.