Wie die Zeit Teil eines Datetime-Wert (SQL Server) entfernen?

stimmen
77

Hier ist, was ich benutze:

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

Ich denke, es kann ein besserer und elegantere Weg.

Bedarf:

  • Es hat so schnell wie möglich (die weniger Gießen, desto besser) sein.
  • Das Endergebnis ist ein zu datetimeTyp, kein String.
Veröffentlicht am 05/08/2008 um 21:08
quelle vom benutzer
In anderen Sprachen...                            


6 antworten

stimmen
106

SQL Server 2008 oder höher

In SQL Server 2008 und höher, natürlich ist der schnellste Weg Convert(date, @date). Dies ist auf einem gegossen werden datetimeoder , datetime2falls erforderlich.

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

Ich habe über inkonsistente Ansprüche gesehen, was am schnellsten für die Zeit von einem Datum in SQL Server Kürzen, und einige Leute sogar gesagt, haben sie Tests, aber meine Erfahrung hat anders. Lassen Sie uns also etwas strengere Tests tun und lassen Sie alle das Skript so, wenn ich keine Fehler machen Leute mich korrigieren.

Float Conversions sind nicht korrekt

Erstens würde ich bleibe weg von Umwandlung datetimezu float, weil es nicht richtig konvertieren. Sie können mit dabei die zeit Entfernung Sache genau, weg , aber ich denke , dass es eine schlechte Idee ist , es zu benutzen , weil es in Verbindung steht implizit die Entwickler , dass dies ein sicherer Betrieb ist und es 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

Das ist nicht etwas, das wir Menschen in unserem Code oder in unseren Beispielen lehren Online werden sollen.

Auch ist es nicht einmal der schnellste Weg!

Proof - Performance Testing

Wenn Sie einige Tests durchführen wollen, um sie zu sehen, wie die verschiedenen Methoden wirklich stapeln, dann werden Sie den Setup-Skript müssen die Tests weiter unten ausgeführt wird:

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 dies eine in Ihrer Datenbank 427,57 MB Tabelle erstellt und so etwas wie 15-30 Minuten in Anspruch nehmen zu laufen. Wenn Ihre Datenbank auf 10% Wachstum kleiner und eingestellt ist, wird es länger dauern, als wenn Sie groß genug, um erste Größe.

Jetzt für die tatsächliche Leistung Testskript. Bitte beachten Sie, dass es sinnvoll ist, um nicht Zeilen zurück an den Client zurück, da dies auf 26 Millionen Zeilen teuer ist verrückt und würde die Leistungsunterschiede zwischen den Methoden verbergen.

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 Analyse

Einige Hinweise dazu. Zunächst einmal, wenn nur ein GROUP BY oder einen Vergleich durchgeführt wird , gibt es keine Notwendigkeit zu konvertieren zurück datetime. So können Sie einige CPU speichern , dass durch die Vermeidung, es sei denn , Sie den endgültigen Wert für die Anzeige benötigen. Sie können sogar GROUP BY nicht umgesetzten Wert und setzen die Umwandlung nur in der SELECT - Klausel:

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

Siehe auch, wie die numerischen Konvertierungen nur etwas mehr Zeit in Anspruch nehmen zu konvertieren wieder zu datetime, aber die varcharUmwandlung fast verdoppelt? Dies zeigt den Teil der CPU, die bisher Berechnung in den Abfragen gewidmet. Es gibt Teile der CPU - Auslastung, die keine Datumsberechnung einzubeziehen und dies scheint etwas in der Nähe 19875 ms in den obigen Abfragen zu sein. Dann erfolgt die Umsetzung einen zusätzlichen Betrag, so dass , wenn es zwei Umwandlungen, wird dieser Betrag etwa doppelt verbraucht.

Weitere Untersuchung zeigt , dass im Vergleich zu Convert(, 112)der Convert(, 101)Abfrage einige zusätzliche CPU Kosten hat (da es eine längere verwendet varchar?), Weil die zweite Rückumwandlung in datenicht so viel kosten wie die anfängliche Umwandlung zu varchar, aber mit Convert(, 112)ihm ist näher an der gleichen 20000 ms CPU Grundkosten.

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

     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
  • Runde ist die CPU - Zeit für eine Rundreise zurück zu datetime.

  • einzelnes sind die CPU - Zeit für eine einzelne Umwandlung in den alternativen Datentyp (die, die den Nebeneffekt des Entfernens den Zeitabschnittes).

  • Basis ist die Berechnung der sich aus der Subtraktion singlezwischen den beiden Invokationen der Differenz: single - (round - single). Es ist eine grobe Schätzung, die die Umwandlung zu und von diesem Datentyp annimmt und datetimeist ungefähr das gleiche in jede Richtung. Es scheint diese Annahme nicht perfekt ist , aber in der Nähe , da die Werte alle in der Nähe 20000 ms mit nur einer Ausnahme sind.

Ein interessanter ist , dass die Grundkosten für die einzelne nahezu gleich sind Convert(date)Methode (das fast 0 Kosten sein muss, als der Server intern kann den ganzzahligen Teil Tag direkt aus dem ersten vier Bytes des Extraktes datetimeDatentypen).

Fazit

So wie es aussieht , ist , dass die einzelnen varcharRichtungsumwandlungsverfahren dauert etwa 1,8 & mgr; s und die Single-Richtung DateDiffMethode dauert etwa 0,18 us. Ich bin stützen diese auf der konservativsten „Basis - CPU“ Zeit in meinem Test von 18.458 ms insgesamt für 25.920.000 Zeilen, so 23218 ms / 25.920.000 = 0,18 us. Die scheinbare 10x Verbesserung scheint wie eine Menge, aber es ist ehrlich gesagt ziemlich klein , bis Sie mit hunderttausenden von Zeilen (617k Zeilen = 1 Sekunde Einsparungen) handeln.

Auch angesichts dieser kleine absolute Verbesserung, meiner Meinung nach , die DateAddgewinnt Methode , weil es die beste Kombination aus Leistung und Klarheit ist. Die Antwort , die von einer „magischen Zahl“ erfordert 0.50000004wird jemand einen Tag (fünf Nullen oder sechs ???) beißen, und es ist schwieriger zu verstehen.

Zusätzliche Bemerkungen

Wenn ich einige Zeit erhalten werde ich ändern , 0.50000004um '12:00:00.003'zu sehen , wie es funktioniert. Es wird auf den gleichen umgewandelt datetimeWert und ich finde es viel leichter zu merken.

Für Interessenten wurden die obigen Tests auf einem Server ausführen, auf die @@ Version gibt folgende Formel:

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)

Beantwortet am 12/09/2010 um 23:57
quelle vom benutzer

stimmen
27

SQL Server 2008 hat einen neuen Datum Datentyp und dies vereinfacht dieses Problem:

SELECT CAST(CAST(GETDATE() AS date) AS datetime)
Beantwortet am 06/08/2008 um 07:44
quelle vom benutzer

stimmen
16

Itzik Ben-Gan in DATETIME- Berechnungen, Teil 1 (SQL Server Magazine, Februar 2007) zeigt drei Methoden zur Durchführung einer solchen Umwandlung ( langsamsten schnellsten , die Differenz zwischen dem zweiten und dritten Verfahren ist klein):

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)

Ihre Technik (Gießen zu schwimmen ) von einem Leser in der Ausgabe des Magazins April vorgeschlagen. Ihm zufolge hat es die Leistung vergleichbar mit der zweiten Technik , die oben dargestellt.

Beantwortet am 06/08/2008 um 09:06
quelle vom benutzer

stimmen
11

Ihr CAST- FLOOR- CASTschon scheint der optimale Weg, zumindest auf MS SQL Server 2005 zu sein.

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

Beantwortet am 05/08/2008 um 21:12
quelle vom benutzer

stimmen
3

Bitte versuche:

SELECT CONVERT(VARCHAR(10),[YOUR COLUMN NAME],105) [YOURTABLENAME]
Beantwortet am 29/06/2013 um 10:49
quelle vom benutzer

stimmen
0

SQL2005: Ich empfehle statt dateadd gegossen. Beispielsweise,

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

rund 10% im Durchschnitt schneller auf meinem Datensatz als

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

(Und in Gießen war schneller small noch)

Beantwortet am 05/11/2014 um 04:26
quelle vom benutzer

Cookies help us deliver our services. By using our services, you agree to our use of cookies. Learn more