Hoe te verwijderen van de tijd deel van een datetime-waarde (SQL Server)?

Hier is wat ik gebruik:

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

Ik denk er kan een betere en meer elegante manier.

Eisen:

  • Het moet zo snel mogelijk (de minder gieten, hoe beter).
  • Het uiteindelijke resultaat is een datetime type, niet een string.

 

6 Replies
  1. 114

    SQL Server 2008 en

    In SQL Server 2008 en natuurlijk de snelste manier is Convert(date, @date). Dit kan worden gegoten terug naar een datetime of datetime2 indien nodig.

    Wat Is Echt de Beste In SQL Server 2005 en Ouder?

    Ik heb gezien inconsistente beweringen over wat de snelste voor het afkappen van de tijd van een datum in SQL Server, en sommige mensen zeiden zelfs dat ze deden testen, maar mijn ervaring is anders. Dus laten we doen wat strengere testen en laat iedereen heeft het script dus als ik fouten maak mensen kan het juist mij.

    Float Conversies Zijn Niet Nauwkeurig

    Eerste, ik zou wegblijven van het omzetten van datetime te float, omdat het niet correct geconverteerd. Je kan wegkomen met het doen van de time-verwijdering ding precies, maar ik denk dat het een slecht idee om het te gebruiken omdat het impliciet communiceert aan ontwikkelaars dat dit een veilige werking en het is niet. Neem een kijkje:

    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

    Dit is niet iets wat we moeten het onderwijzen van mensen in onze code of in onze voorbeelden online.

    Ook, het is zelfs niet de snelste manier!

    Bewijs – Performance Testen

    Als u voor het uitvoeren van een aantal tests jezelf om te zien hoe de verschillende methoden echt stack up, dan moet u deze installatie script voor het uitvoeren van de tests verder naar beneden:

    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

    Let op: dit creëert een 427.57 MB tabel in uw database en zal iets van 15-30 minuten te lopen. Als uw database is klein en ingesteld op 10% groei het zal langer duren dan als u de grootte groot genoeg eerste.

    Nu voor de werkelijke performance test script. Let op: het is zinvol om niet terug rijen terug naar de client als deze is gek duur op 26 miljoen rijen en zou verbergen van de prestatie verschillen tussen de methoden.

    Resultaten

    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;

    Een Aantal Wandel-Analyse

    Een aantal opmerkingen over dit. Eerste van alles, als het uitvoeren van een GROEP of van een vergelijking, er is geen noodzaak om terug te zetten naar datetime. Zodat u kunt sparen voor sommige CPU, door te voorkomen dat, tenzij u de definitieve waarde voor weergave doeleinden. U kunt zelfs een GROEP DOOR de niet-geconverteerde waarde en om de conversie alleen in de SELECT-component:

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

    Ook zien hoe de numerieke conversies alleen iets meer tijd om terug te zetten naar datetime, maar de varchar conversie bijna verdubbelt? Dit wijst op het gedeelte van de CPU, die is gewijd aan de datum van de berekening in de query ‘ s. Er zijn delen van het CPU-gebruik waarbij geen sprake datum berekening, en dit lijkt iets wat dicht bij 19875 ms in de bovenstaande query ‘ s. Dan is de conversie neemt een aantal extra bedrag, dus als er twee conversies, dat bedrag is ongeveer twee keer.

    Meer onderzoek blijkt dat in vergelijking met Convert(, 112), de Convert(, 101) query heeft een aantal extra CPU-kosten (omdat het gebruik maakt van een langere varchar?), omdat de tweede conversie terug naar date niet zo veel kost als de eerste conversie te varchar, maar met Convert(, 112) het is dichter bij de dezelfde 20000 ms CPU-base kosten.

    Hier zijn de berekeningen op de CPU-tijd die ik heb gebruikt voor de bovenstaande 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
    • ronde is de CPU-tijd voor een round trip terug naar datetime.

    • één is de CPU-tijd voor een enkele bekering tot het alternatieve data type (dat is de kant effect van het verwijderen van de tijd deel).

    • basis is de berekening van de af te trekken van single het verschil tussen de twee aanroepen: single - (round - single). Het is een ballpark figure die uitgaat van de conversie naar en van die gegevens type en datetime is ongeveer hetzelfde in beide richtingen. Het lijkt deze veronderstelling is niet perfect, maar is dicht omdat de waarden zijn allemaal in de buurt van 20000 ms met slechts één uitzondering.

    Een interessant ding is dat de basiskosten is bijna gelijk aan de single Convert(date) methode (dat is bijna 0 kosten, als de server kan intern extract de gehele dag deel uit van de eerste vier bytes van de datetime data type).

    Conclusie

    Dus hoe het eruit ziet, is dat de één-richting varchar conversie methode duurt ongeveer 1.8 µs en met de één-richting DateDiff methode duurt ongeveer 0.18 µs. Ik ben baseren dit op de meest conservatieve “base CPU” in mijn testen van 18458 ms totaal voor 25,920,000 rijen, dus 23218 ms /25920000 = 0.18 µs. De schijnbare 10x verbetering lijkt veel, maar het is eerlijk gezegd vrij klein tot u te maken met honderden of duizenden rijen (617k rijen = 1 seconde besparingen).

    Zelfs dit kleine absolute verbetering, in mijn mening, de DateAdd methode wint want het is de beste combinatie van prestaties en helderheid. Het antwoord dat vereist een “magic number” van 0.50000004 is van plan om te bijten iemand een aantal dagen (vijf of zes nullen???), plus het is moeilijker om te begrijpen.

    Aanvullende Opmerkingen

    Wanneer ik een aantal keer ben ik van plan om te veranderen 0.50000004 te '12:00:00.003' en zie hoe het werkt. Het wordt geconverteerd naar dezelfde datetime waarde en ik vind het veel gemakkelijker om te onthouden.

    Voor diegenen die geïnteresseerd zijn, is de bovengenoemde tests werden uitgevoerd op een server waar @@Version geeft het volgende:

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

    • +1 voor Welke versie van SQL Server heb je dit testen door de manier?
    • Het lijkt erop dat je single en rond naar achteren in de tabel. Ook is er een verschil in tijd als u gebruik char in plaats van varchar?
    • dank, vast. Char blijkt precies hetzelfde als varchar.
    • In Oracle er select round(sysdate) from dual en we zeker nodig die in Sql Server.
    • +1 ik vond het punt dat float conversies zijn niet juist
    • in Oracle is er de eenvoudige trunc() functie. Als u gebruik ronde, ik denk dat je vindt dat de tijd na 12 uur ‘ s middags zijn afgerond tot de volgende dag in plaats van de huidige datum.
    • bedankt! In feite, kunt u mij de ogen geopend naar de betekenis van dit trefwoord in de context van datum-tijd.
    • +1 grote antwoord, maar niet converteren-to-date werken sneller dan datediff?
    • Als u werkt met SQL Server 2008 en, ja, het converteren naar date gegevens type is het snelste, zoals weergegeven in mijn tests hierboven.
    • Downvoter stuur een reactie? Lachen!
    • Het zou interessant zijn te weten het tegenovergestelde: wat is de beste manier om alleen de tijd?
    • Tijd deel als “HH:MM:SS” en “UU:MM” – varianten, als die maakt dat een verschil?
    • Vraag een nieuwe vraag. Voel je vrij om commentaar met een link.
    • gedaan. Zie stackoverflow.com/questions/40193719/….

  2. 30

    SQL Server 2008 is een nieuwe datum data type en dit vereenvoudigt dit probleem:

    SELECT CAST(CAST(GETDATE() AS date) AS datetime)
    • Ik had per ongeluk zijn ingevoerd, 0218 in plaats van 2018 als het jaar en de DATEADD(DATEDIFF()) methode om te snijden van de tijd deel genereert een uitzondering. Toen wierp ik het resultaat terug naar datetime2 uw methode werkt goed select cast(CAST(convert(datetime2(0), '0218-09-12', 120) AS date) as datetime2)
  3. 18

    Itzik Ben-Gan in DATETIME Berekeningen, Deel 1 (SQL Server-Magazine, februari 2007) toont drie methoden van het uitvoeren van een dergelijke conversie (langzaamste naar snelste; het verschil tussen de tweede en de derde methode is 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)

    Je techniek (casting naar float) wordt voorgesteld door een lezer in het aprilnummer van het magazine. Volgens hem heeft prestaties die vergelijkbaar zijn met die van de tweede techniek die hierboven.

    • In mijn mening casting te zweven is het niet best. Stuur een zie mijn antwoord
    • Ik ga ermee akkoord dat de 3e methode is zeer obscure omdat van de 0.50000004 – waarde, maar het is de snelste en uw tests bevestigen dat. Dus, het voldoet aan de zo snel als mogelijk eis.
    • Ook hier is wat het artikel dat ik gekoppeld zegt over de 0.50000004, waarde: Hoewel deze uitdrukking is een korte (en efficiënt, zoals ik zal aantonen kort), ik moet zeggen dat ik me ongemakkelijk met het. Ik weet niet of ik kan mijn vinger op precies waarom, misschien omdat het te technisch, en je kunt het niet zien datetime gerelateerde logica in.
    • Als we gaan deze methode wilt gebruiken, zou ik liever SELECT CAST(CAST(GETDATE() - '12:00:00.003' AS int) AS datetime) in plaats daarvan, als het betekent dat iets voor mij en het is imo veel gemakkelijker om te onthouden.
    • Dit is nu het snelst in SQL 2008: Convert(date, GetDate()).
  4. 12

    Uw CASTFLOORCAST al lijkt de beste manier is, ten minste op MS SQL Server 2005.

    Enkele andere oplossingen die ik heb gezien hebben een string-conversie, zoals Select Convert(varchar(11), getdate(),101) in hen, die langzamer door een factor van 10.

    • We gebruiken de methode voorgesteld door Michael Stum in één van onze producten en het werkt als een charme.
    • Dit is niet de beste manier is, door nogal wat. Zie mijn antwoord op dezelfde pagina.
  5. 1

    SQL2005: ik adviseer gegoten in plaats van dateadd. Bijvoorbeeld,

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

    gemiddeld rond de 10% sneller op mijn set, dan

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

    (en gieten in smalldatetime was nog sneller)

Geef een reactie

Het e-mailadres wordt niet gepubliceerd. Vereiste velden zijn gemarkeerd met *