Come rimuovere la parte relativa all’ora di un valore datetime (SQL Server)?

Ecco cosa uso:

SELECT CAST(FLOOR(CAST(getdate() as FLOAT)) as DATETIME) 

Sto pensando che potrebbe esserci un modo migliore e più elegante.

Requisiti:

  • Deve essere il più veloce ansible (minore è il casting, meglio è).
  • Il risultato finale deve essere un tipo datetime , non una stringa.

SQL Server 2008 e versioni successive

In SQL Server 2008 e versioni successive, ovviamente il modo più veloce è Convert(date, @date) . Questo può essere ricondotto a un datetime o datetime2 se necessario.

Cosa è davvero meglio in SQL Server 2005 e versioni precedenti?

Ho visto affermazioni incoerenti su ciò che è più veloce per troncare il tempo da una data in SQL Server, e alcune persone hanno persino affermato di aver fatto dei test, ma la mia esperienza è stata diversa. Facciamo test più severi e lasciamo a tutti la sceneggiatura, quindi se commetto errori, le persone possono correggermi.

Le conversioni fluttuanti non sono accurate

In primo luogo, starei lontano dalla conversione di datetime in float , perché non converte correttamente. Potresti farla franca con la procedura di rimozione del tempo in modo accurato, ma penso che sia una ctriggers idea usarla perché comunica implicitamente agli sviluppatori che si tratta di un’operazione sicura e non lo è . Guarda:

 declare @d datetime; set @d = '2010-09-12 00:00:00.003'; select Convert(datetime, Convert(float, @d)); -- result: 2010-09-12 00:00:00.000 -- oops 

Questo non è qualcosa che dovremmo insegnare alle persone nel nostro codice o nei nostri esempi online.

Inoltre, non è nemmeno il modo più veloce!

Prova – Test delle prestazioni

Se vuoi eseguire alcuni test tu stesso per vedere come i diversi metodi si impongono realmente, allora avrai bisogno di questo script di installazione per eseguire i test più in basso:

 create table AllDay (Tm datetime NOT NULL CONSTRAINT PK_AllDay PRIMARY KEY CLUSTERED); declare @d datetime; set @d = DateDiff(Day, 0, GetDate()); insert AllDay select @d; while @@ROWCOUNT != 0 insert AllDay select * from ( select Tm = DateAdd(ms, (select Max(DateDiff(ms, @d, Tm)) from AllDay) + 3, Tm) from AllDay ) X where Tm < DateAdd(Day, 1, @d); exec sp_spaceused AllDay; -- 25,920,000 rows 

Si noti che questo crea una tabella 427,57 MB nel database e richiederà circa 15-30 minuti per l'esecuzione. Se il tuo database è piccolo e impostato su una crescita del 10%, ci vorrà più tempo se prima hai dimensioni abbastanza grandi.

Ora per lo script di test delle prestazioni effettivo. Si noti che è utile non restituire righe al client poiché questo è follemente costoso su 26 milioni di righe e hidebbe le differenze di prestazioni tra i metodi.

Risultati delle prestazioni

 set statistics time on; -- (All queries are the same on io: logical reads 54712) GO declare @dd date, @d datetime, @di int, @df float, @dv varchar(10); -- Round trip back to datetime select @d = CONVERT(date, Tm) from AllDay; -- CPU time = 21234 ms, elapsed time = 22301 ms. select @d = CAST(Tm - 0.50000004 AS int) from AllDay; -- CPU = 23031 ms, elapsed = 24091 ms. select @d = DATEDIFF(DAY, 0, Tm) from AllDay; -- CPU = 23782 ms, elapsed = 24818 ms. select @d = FLOOR(CAST(Tm as float)) from AllDay; -- CPU = 36891 ms, elapsed = 38414 ms. select @d = CONVERT(VARCHAR(8), Tm, 112) from AllDay; -- CPU = 102984 ms, elapsed = 109897 ms. select @d = CONVERT(CHAR(8), Tm, 112) from AllDay; -- CPU = 103390 ms, elapsed = 108236 ms. select @d = CONVERT(VARCHAR(10), Tm, 101) from AllDay; -- CPU = 123375 ms, elapsed = 135179 ms. -- Only to another type but not back select @dd = Tm from AllDay; -- CPU time = 19891 ms, elapsed time = 20937 ms. select @di = CAST(Tm - 0.50000004 AS int) from AllDay; -- CPU = 21453 ms, elapsed = 23079 ms. select @di = DATEDIFF(DAY, 0, Tm) from AllDay; -- CPU = 23218 ms, elapsed = 24700 ms select @df = FLOOR(CAST(Tm as float)) from AllDay; -- CPU = 29312 ms, elapsed = 31101 ms. select @dv = CONVERT(VARCHAR(8), Tm, 112) from AllDay; -- CPU = 64016 ms, elapsed = 67815 ms. select @dv = CONVERT(CHAR(8), Tm, 112) from AllDay; -- CPU = 64297 ms, elapsed = 67987 ms. select @dv = CONVERT(VARCHAR(10), Tm, 101) from AllDay; -- CPU = 65609 ms, elapsed = 68173 ms. GO set statistics time off; 

Alcune analisi vaganti

Alcune note su questo. Prima di tutto, se esegui solo un GROUP BY o un confronto, non è necessario riconvertire in datetime . Così puoi risparmiare un po 'di CPU evitandolo, a meno che tu non abbia bisogno del valore finale per scopi di visualizzazione. Puoi anche GROUP BY il valore non convertito e inserire la conversione solo nella clausola SELECT:

 select Convert(datetime, DateDiff(dd, 0, Tm)) from (select '2010-09-12 00:00:00.003') X (Tm) group by DateDiff(dd, 0, Tm) 

Inoltre, vedi come le conversioni numeriche richiedono solo un po 'più di tempo per riconvertire in datetime , ma la conversione varchar quasi raddoppia? Questo rivela la parte della CPU dedicata al calcolo della data nelle query. Ci sono parti dell'utilizzo della CPU che non implicano il calcolo della data, e questo sembra essere qualcosa di simile a 19875 ms nelle query sopra. Quindi la conversione richiede un importo aggiuntivo, quindi se ci sono due conversioni, tale importo viene utilizzato circa due volte.

Un altro esame rivela che rispetto a Convert(, 112) , la query Convert(, 101) ha un costo aggiuntivo per la CPU (poiché utilizza un varchar più lungo?), Perché la seconda conversione di nuovo non costa quanto l'iniziale conversione in varchar , ma con Convert(, 112) è più vicino allo stesso costo di base della CPU di 20000 ms.

Ecco i calcoli sul tempo della CPU che ho usato per l'analisi di cui sopra:

  method round single base ----------- ------ ------ ----- date 21324 19891 18458 int 23031 21453 19875 datediff 23782 23218 22654 float 36891 29312 21733 varchar-112 102984 64016 25048 varchar-101 123375 65609 7843 
  • round è il tempo della CPU per un viaggio di ritorno a datetime .

  • single è il tempo della CPU per una singola conversione al tipo di dati alternativo (quello che ha l'effetto collaterale di rimuovere la porzione temporale).

  • base è il calcolo della sottrazione dalla single differenza tra le due invocazioni: single - (round - single) . Si tratta di una figura del ballpark che presuppone la conversione da e verso quel tipo di dati e il datetime è approssimativamente lo stesso in entrambe le direzioni. Sembra che questa ipotesi non sia perfetta ma è vicina perché i valori sono tutti vicini a 20000 ms con una sola eccezione.

Un'altra cosa interessante è che il costo di base è quasi uguale al singolo metodo di Convert(date) (che deve essere quasi pari a 0, in quanto il server può estrarre internamente la porzione di giorno intero a destra dei primi quattro byte dei dati datetime genere).

Conclusione

Quindi, a quanto pare, il metodo di conversione varchar direzione richiede circa 1,8 μs e il metodo DateDiff direzione richiede circa 0,18 μs. Sto basando questo sul tempo di "CPU base" più conservativo nel mio test di 18458 ms totali per 25.920.000 righe, quindi 23218 ms / 25920000 = 0.18 μs. L'apparente miglioramento 10x sembra molto, ma è francamente abbastanza piccolo fino a quando non si ha a che fare con centinaia di migliaia di righe (617k righe = 1 secondo di risparmio).

Anche dato questo piccolo miglioramento assoluto, a mio parere, il metodo DateAdd vince perché è la migliore combinazione di prestazioni e chiarezza. La risposta che richiede un "numero magico" di 0.50000004 morderà qualcuno un giorno (cinque zeri o sei ???), in più è più difficile da capire.

Note aggiuntive

Quando avrò tempo, cambierò 0.50000004 in '12:00:00.003' e vedremo come funziona. Viene convertito nello stesso valore datetime e lo trovo molto più facile da ricordare.

Per chi fosse interessato, i suddetti test sono stati eseguiti su un server in cui @@ Version restituisce quanto segue:

Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) 9 luglio 2008 14:43:34 Copyright (c) 1988-2008 Edizione standard Microsoft Corporation su Windows NT 5.2 (Build 3790: Service Pack 2)

SQL Server 2008 ha un nuovo tipo di dati di data e questo semplifica questo problema per:

 SELECT CAST(CAST(GETDATE() AS date) AS datetime) 

Itzik Ben-Gan in DATETIME Calculations, Parte 1 (SQL Server Magazine, febbraio 2007) mostra tre metodi per eseguire tale conversione (dal più lento al più veloce , la differenza tra il secondo e il terzo metodo è piccola):

 SELECT CAST(CONVERT(char(8), GETDATE(), 112) AS datetime) SELECT DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0) SELECT CAST(CAST(GETDATE() - 0.50000004 AS int) AS datetime) 

La tua tecnica (casting to float ) è suggerita da un lettore nel numero di aprile della rivista. Secondo lui, ha prestazioni paragonabili a quelle della seconda tecnica presentata sopra.

Il tuo CASTFLOORCAST sembra già essere il modo migliore, almeno su MS SQL Server 2005.

Alcune altre soluzioni che ho visto hanno una conversione di stringhe, come Select Convert(varchar(11), getdate(),101) in esse, che è più lento di un fattore di 10.

Per favore prova:

 SELECT CONVERT(VARCHAR(10),[YOUR COLUMN NAME],105) [YOURTABLENAME] 

SQL2005: raccomando cast invece di dateadd. Per esempio,

 select cast(DATEDIFF(DAY, 0, datetimefield) as datetime) 

in media circa il 10% più veloce sul mio set di dati, rispetto a

 select DATEADD(DAY, DATEDIFF(DAY, 0, datetimefield), 0) 

(e il casting in smalldatetime era ancora più veloce)