Hvordan fjerne tiden del av en datetime-verdi (SQL Server)?

stemmer
77

Her er hva jeg bruker:

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

Jeg tenker det kan være en bedre og mer elegant måte.

Krav:

  • Det må være så hurtig som mulig (mindre støpe, desto bedre).
  • Det endelige resultatet må være en datetimeattraksjon, ikke en streng.
Publisert på 05/08/2008 klokken 20:08
kilden bruker
På andre språk...                            


6 svar

stemmer
106

SQL Server 2008 og opp

I SQL Server 2008 og opp, er selvfølgelig den raskeste måten Convert(date, @date). Dette kan bli kastet tilbake til en datetimeeller datetime2hvis det er nødvendig.

Hva er egentlig best i SQL Server 2005 og eldre?

Jeg har sett inkonsekvente påstander om hva som er raskest for å avkorte tiden fra en dato i SQL Server, og noen folk selv sa de gjorde testing, men min erfaring har vært annerledes. Så la oss gjøre noe strengere testing og la alle ha manuset så hvis jeg gjør noen feil folk kan korrigere meg.

Float Konverteringer er ikke nøyaktig

Først, jeg ville holde seg borte fra å konvertere datetimetil float, fordi det ikke konverterer riktig. Du kan komme unna med å gjøre tids fjerning ting nøyaktig, men jeg tror det er en dårlig idé å bruke det fordi det implisitt kommuniserer til utviklerne at dette er en sikker drift og det er ikke . Ta en titt:

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

Dette er ikke noe vi skal lære folk i vår kode eller i våre eksempler på nettet.

Dessuten er det ikke engang den raskeste måten!

Proof - Performance Testing

Hvis du ønsker å utføre noen tester selv for å se hvordan de forskjellige metodene virkelig stable opp, så du trenger dette oppsettet skript for å kjøre testene lenger ned:

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

Vær oppmerksom på at dette skaper en 427,57 MB tabell i databasen og vil ta noe sånt som 15-30 minutter å kjøre. Hvis databasen er liten og satt til 10% vekst vil det ta lengre tid enn hvis du størrelsen stor nok først.

Nå for den faktiske ytelsestesting script. Vær oppmerksom på at det er målrettet for å ikke returnere rader tilbake til klienten som dette er vanvittig dyrt på 26 millioner rader og ville skjule prestasjonsforskjellene mellom metodene.

RESULTATENE

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;

Noen Rambling Analysis

Noen notater om dette. Først av alt, hvis bare utføre et GROUP BY eller en sammenligning, er det ingen grunn til å konvertere tilbake til datetime. Så du kan spare CPU ved å unngå det, med mindre du trenger den endelige verdien for visningsformål. Du kan til og med GROUP BY de uomvendte verdi og sette konverteringen bare i SELECT-leddet:

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

Se også hvordan de numeriske konverter bare ta litt mer tid til å konvertere tilbake til datetime, men varcharkonverteringen dobler nesten? Dette avslører den delen av CPU som er viet til dato beregning i spørringene. Det er deler av CPU-bruk som ikke involverer dato beregning, og dette ser ut til å være noe nær 19875 ms i de ovennevnte spørsmål. Deretter konverteringen tar litt ekstra beløp, så hvis det er to konverteringer, at beløpet er brukt opp omtrent to ganger.

Mer undersøkelse avslører at i forhold til Convert(, 112), den Convert(, 101)har spørringen noen ekstra CPU kostnad (siden den bruker lengre varchar?), Fordi den andre konvertering tilbake til dateikke koste så mye som den første konvertering til varchar, men med Convert(, 112)det er nærmere det samme 20000 ms CPU grunnkostnad.

Her er de beregninger på CPU-tid som jeg brukte for ovennevnte analyse:

     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 er CPU tid for en rundtur tilbake til datetime.

  • enkelt er CPU-tiden for en enkelt omdannelse til den alternative datatype (den som har den bivirkning av fjerning av tidsdelen).

  • basen er beregningen av subtrahere fra singleforskjellen mellom de to kjøringene: single - (round - single). Det er et omtrentlig tall som går ut i fra omdannelsen til og fra den datatype og datetimeer omtrent den samme i begge retninger. Det synes denne antakelsen er ikke perfekt, men er nær fordi verdiene er alle nær 20000 ms med bare ett unntak.

En mer interessant er at grunnkostnaden er nesten lik den eneste Convert(date)metode (som må være nesten 0 kostnad, som serveren kan internt trekke ut det hele tall dag del rett ut av de første fire byter av datetimedatatypen).

Konklusjon

Så hvordan det ser ut, er at enkelt retning varcharkonvertering metoden tar ca 1,8 mikrosekunder og enkelt retning DateDiffmetoden tar ca 0,18 mikrosekunder. Jeg basere dette på den mest konservative "base CPU" tid i min testing av 18458 ms totalt for 25,920,000 rader, så 23218 MS / 25920000 = 0,18 ms. Den tilsynelatende 10x forbedring virker som mye, men det er ærlig talt ganske liten før du arbeider med hundretusener av rader (617k p = 1 sekund spare).

Selv gitt denne lille absolutt forbedring, etter min mening, den DateAddvinner metoden fordi det er den beste kombinasjonen av ytelse og klarhet. Svaret som krever en "magisk tall" for 0.50000004kommer til å bite noen en dag (fem nuller eller seks ???), pluss det er vanskeligere å forstå.

flere merknader

Når jeg får litt tid jeg kommer til å endre 0.50000004'12:00:00.003'og se hvordan den gjør. Det blir omdannet til samme datetimeverdi, og jeg synes det er mye lettere å huske.

For de som er interessert, ble de ovennevnte testene kjøres på en server der @@ versjon returnerer følgende:

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

Svarte 12/09/2010 kl. 22:57
kilden bruker

stemmer
27

SQL Server 2008 har en ny dato datatype og dette forenkler dette problemet til:

SELECT CAST(CAST(GETDATE() AS date) AS datetime)
Svarte 06/08/2008 kl. 06:44
kilden bruker

stemmer
16

Itzik Ben-Gan i datetime beregninger, del 1 (SQL Server Magazine, februar 2007) viser tre måter å utføre en slik omdannelse ( langsomste til raskest , forskjellen mellom den andre og tredje metode er liten):

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)

Teknikken (støping for å flyte ) er foreslått av en leser i april-utgaven av magasinet. Ifølge ham, har det ytelse sammenlignes med andre teknikken er presentert ovenfor.

Svarte 06/08/2008 kl. 08:06
kilden bruker

stemmer
11

Din CAST- FLOOR- CASTallerede ser ut til å være den optimale måten, i hvert fall på MS SQL Server 2005.

Noen andre løsninger jeg har sett har en streng-konvertering, som Select Convert(varchar(11), getdate(),101)i dem, noe som er langsommere med en faktor på ti.

Svarte 05/08/2008 kl. 20:12
kilden bruker

stemmer
3

Vennligst prøv:

SELECT CONVERT(VARCHAR(10),[YOUR COLUMN NAME],105) [YOURTABLENAME]
Svarte 29/06/2013 kl. 09:49
kilden bruker

stemmer
0

SQL2005: Jeg anbefaler kastet i stedet for dateadd. For eksempel,

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

gjennomsnitt rundt 10% raskere på min datasettet, enn

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

(Og støping inn i smalldatetime var raskere fortsatt)

Svarte 05/11/2014 kl. 04:26
kilden bruker

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