Wie entferne ich den Zeitanteil eines Datums- / Uhrzeitwerts (SQL Server)?

Translate

Folgendes verwende ich:

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

Ich denke, es gibt vielleicht einen besseren und eleganteren Weg.

Bedarf:

  • Es muss so schnell wie möglich sein (je weniger Casting, desto besser).
  • Das Endergebnis muss a seindatetimeTyp, keine Zeichenfolge.
This question and all comments follow the "Attribution Required."

Alle Antworten

Translate

SQL Server 2008 und höher

In SQL Server 2008 und höher ist natürlich der schnellste WegConvert(date, @date). Dies kann auf a zurückgesetzt werdendatetimeoderdatetime2im Bedarfsfall.

Was ist in SQL Server 2005 und älter wirklich am besten?

Ich habe inkonsistente Behauptungen darüber gesehen, was am schnellsten ist, um die Zeit von einem Datum in SQL Server abzuschneiden, und einige Leute sagten sogar, sie hätten Tests durchgeführt, aber meine Erfahrung war anders. Lassen Sie uns also einige strengere Tests durchführen und jedem das Skript geben, damit die Leute mich korrigieren können, wenn ich Fehler mache.

Float-Konvertierungen sind nicht genau

Erstens würde ich mich vom Konvertieren fernhaltendatetimezufloat, weil es nicht richtig konvertiert. Sie werden vielleicht damit durchkommen, die Zeitentfernung genau durchzuführen, aber ich denke, es ist eine schlechte Idee, sie zu verwenden, da sie den Entwicklern implizit mitteilt, dass dies ein sicherer Vorgang ist undes ist nicht. Schau mal:

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

Dies sollten wir den Menschen nicht in unserem Code oder in unseren Online-Beispielen beibringen.

Auch ist es nicht einmal der schnellste Weg!

Proof - Leistungstests

Wenn Sie einige Tests selbst durchführen möchten, um zu sehen, wie sich die verschiedenen Methoden wirklich stapeln, benötigen Sie dieses Setup-Skript, um die Tests weiter unten auszuführen:

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

Bitte beachten Sie, dass dadurch eine Tabelle mit 427,57 MB in Ihrer Datenbank erstellt wird und die Ausführung etwa 15 bis 30 Minuten dauert. Wenn Ihre Datenbank klein ist und auf 10% Wachstum eingestellt ist, dauert es länger, als wenn Sie zuerst groß genug sind.

Nun zum eigentlichen Leistungstestskript. Bitte beachten Sie, dass es sinnvoll ist, keine Zeilen an den Client zurückzugeben, da dies für 26 Millionen Zeilen verrückt teuer ist und die Leistungsunterschiede zwischen den Methoden verbergen würde.

Leistungsergebnisse

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;

Einige Rambling-Analysen

Einige Anmerkungen dazu. Wenn Sie nur ein GROUP BY oder einen Vergleich durchführen, müssen Sie zunächst nicht zurück konvertierendatetime. Sie können also CPU sparen, indem Sie dies vermeiden, es sei denn, Sie benötigen den endgültigen Wert für Anzeigezwecke. Sie können sogar nach dem nicht konvertierten Wert gruppieren und die Konvertierung nur in die SELECT-Klausel einfügen:

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

Sehen Sie auch, wie die numerische Konvertierung nur geringfügig länger dauert, bis sie wieder konvertiert istdatetime, aber dievarcharUmwandlung fast verdoppelt? Dies zeigt den Teil der CPU, der der Datumsberechnung in den Abfragen gewidmet ist. Es gibt Teile der CPU-Auslastung, die keine Datumsberechnung beinhalten, und dies scheint in den obigen Abfragen in der Nähe von 19875 ms zu liegen. Dann erfordert die Konvertierung einen zusätzlichen Betrag. Wenn also zwei Konvertierungen vorhanden sind, wird dieser Betrag ungefähr zweimal verbraucht.

Mehr Untersuchung zeigt, dass im Vergleich zuConvert(, 112), dasConvert(, 101)Die Abfrage hat einige zusätzliche CPU-Kosten (da sie länger dauertvarchar?), weil die zweite Umstellung zurück aufdatekostet nicht so viel wie die anfängliche Umstellung aufvarchar, aber mitConvert(, 112)Es liegt näher an den gleichen CPU-Grundkosten von 20000 ms.

Hier sind die Berechnungen zur CPU-Zeit, die ich für die obige Analyse verwendet habe:

     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
  • rundenist die CPU-Zeit für einen Roundtrip zurück zudatetime.

  • Singleist die CPU-Zeit für eine einzelne Konvertierung in den alternativen Datentyp (derjenige, der den Nebeneffekt hat, den Zeitanteil zu entfernen).

  • Baseist die Berechnung des Subtrahierens vonsingleder Unterschied zwischen den beiden Aufrufen:single - (round - single). Es ist eine Baseballfigur, die die Konvertierung zu und von diesem Datentyp und voraussetztdatetimeist in beiden Richtungen ungefähr gleich. Es scheint, dass diese Annahme nicht perfekt ist, aber nahe beieinander liegt, da die Werte mit nur einer Ausnahme alle nahe bei 20000 ms liegen.

Eine weitere interessante Sache ist, dass die Grundkosten fast gleich der Single sindConvert(date)Methode (die fast 0 kosten muss, da der Server den Integer-Day-Teil intern direkt aus den ersten vier Bytes des extrahieren kanndatetimeDatentyp).

Fazit

Wie es also aussieht, ist die EinzelrichtungvarcharDas Umwandlungsverfahren dauert etwa 1,8 μs und die EinzelrichtungDateDiffMethode dauert ca. 0,18 μs. Ich stütze mich dabei auf die konservativste "Basis-CPU" -Zeit in meinem Test von insgesamt 18458 ms für 25.920.000 Zeilen, also 23218 ms / 25920000 = 0,18 μs. Die offensichtliche 10-fache Verbesserung scheint viel zu sein, ist aber ehrlich gesagt ziemlich klein, bis Sie mit Hunderttausenden von Zeilen zu tun haben (617.000 Zeilen = 1 Sekunde Einsparung).

Selbst angesichts dieser kleinen absoluten Verbesserung ist meiner Meinung nach dieDateAddMethode gewinnt, weil es die beste Kombination aus Leistung und Klarheit ist. Die Antwort, die eine "magische Zahl" von erfordert0.50000004wird eines Tages jemanden beißen (fünf Nullen oder sechs ???), und es ist schwieriger zu verstehen.

Zusätzliche Bemerkungen

Wenn ich etwas Zeit habe, werde ich mich ändern0.50000004zu'12:00:00.003'und sehen, wie es geht. Es wird in das gleiche konvertiertdatetimeWert und ich finde es viel einfacher, sich zu erinnern.

Für Interessenten wurden die oben genannten Tests auf einem Server ausgeführt, auf dem @@ Version Folgendes zurückgibt:

Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) 9. Juli 2008 14:43:34 Copyright (c) 1988-2008 Microsoft Corporation Standard Edition unter Windows NT 5.2 (Build 3790: Service Pack 2)

Quelle
Translate

SQL Server 2008 hat eine neueDatumDatentypund dies vereinfacht dieses Problem zu:

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

Itzik Ben-Gan inDATETIME-Berechnungen, Teil 1(SQL Server Magazine, Februar 2007) zeigt drei Methoden zum Durchführen einer solchen Konvertierung (am langsamsten bis am schnellsten;; der Unterschied zwischen der zweiten und dritten Methode ist gering):

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)

Deine Technik (Casting zuschweben) wird von einem Leser in der April-Ausgabe des Magazins vorgeschlagen. Ihm zufolge hat es eine Leistung, die mit der der oben vorgestellten zweiten Technik vergleichbar ist.

Quelle
Translate

IhreCAST-FLOOR-CASTscheint bereits der optimale Weg zu sein, zumindest unter MS SQL Server 2005.

Einige andere Lösungen, die ich gesehen habe, haben eine String-Konvertierung, wie zSelect Convert(varchar(11), getdate(),101)in ihnen, die um den Faktor 10 langsamer ist.

Quelle
Translate

Bitte versuche:

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

SQL2005: Ich empfehle Cast anstelle von Dateadd. Zum Beispiel,

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

durchschnittlich rund 10%schnellerauf meinem Datensatz, als

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

(und das Casting in die Smalldatetime war noch schneller)

Quelle