Jak odstranit časovou část datetime hodnota (SQL Server)?

hlasů
77

Tady je to, co mám použít:

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

Myslím, že může být lepší a elegantní způsob.

požadavky:

  • Musí to být tak rychle, jak je to možné (na méně odlitku, tím lépe).
  • Konečný výsledek musí být datetimetypu, není řetězec.
Položena 05/08/2008 v 21:08
zdroj uživatelem
V jiných jazycích...                            


6 odpovědí

hlasů
106

SQL Server 2008 a vyšší

SQL Server 2008 a nahoru, samozřejmě nejrychlejší způsob, jak je Convert(date, @date). To může být odlita zpět na datetimenebo datetime2pokud je to nutné.

Co je opravdu nejlepší SQL Server 2005 a starší?

Viděl jsem rozporuplné tvrzení o tom, co je nejrychlejší pro zkrácení doby od data v SQL Server, a někteří lidé dokonce řekli, že testování, ale moje zkušenost byla jiná. Takže pojďme udělat nějaké přísnější zkoušky a ať všichni mají scénář, takže když udělám nějaké chyby mohou lidé mě opravte.

Float Konverze nejsou přesné

Za prvé, chtěl bych zůstat daleko od převedení datetimedo float, protože to nepřevádí správně. Můžete dostat pryč s tím věc časově odstranění přesně, ale myslím, že je to špatný nápad, aby ji používat, protože to implicitně sděluje vývojáře, že se jedná o bezpečný provoz a to není . Podívej se:

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

To není něco, co bychom měli učit lidi v našem kódu, nebo v našich příkladech online.

Také to není ani nejrychlejší cesta!

Proof - Testování užitkovosti

Chcete-li provést nějaké testy sami vidět, jak se různé metody opravdu vyrovnat, pak budete potřebovat tento instalační skript spouštět testy kus dál:

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

Upozorňujeme, že toto vytvoří 427,57 MB tabulku v databázi a bude mít něco podobného 15-30 minut běžet. Pokud databáze je malá a nastaven na 10% růstu bude trvat déle, než když se dimenzovat dostatečně velký jako první.

Nyní k vlastnímu testování výkonu scénář. Vezměte prosím na vědomí, že je účelné, aby nevrátí řádky zpět klientovi, jak je to šílené drahé na 26 miliónů řádků a bude skrývat rozdíly výkonnosti mezi metodami.

Výsledky výkonnosti

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;

Některé Rambling Analysis

Některé poznámky o tom. Za prvé, pokud právě vykonává GROUP BY nebo srovnávání, není třeba převádět zpět datetime. Takže můžete ušetřit nějaké CPU vyloučením, že pokud budete potřebovat konečnou hodnotu pro účely zobrazení. Můžete dokonce GROUP BY nepřevedených hodnotu a dal konverzi pouze v klauzuli 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)

Také, jak numerické konverze trvat jen trochu více času převést zpět datetime, ale varcharkonverze téměř zdvojnásobí? To ukazuje část procesoru, který je věnován výpočtu data v dotazech. Existují části využití procesoru, které nezahrnují výpočet data, a zdá se, že něco podobného 19875 ms ve výše uvedených dotazů. Pak převod trvá nějakou dodatečnou částku, takže v případě, že jsou dvě konverze, tato částka se použije až přibližně dvakrát.

Další zkoumání ukazuje, že ve srovnání Convert(, 112)se Convert(, 101)dotaz má některé dodatečné náklady procesoru (protože se používá již varchar?), Protože druhý převod zpět do datenestojí tolik jako počáteční konverzí na varchar, ale s Convert(, 112)ním je blíže ke stejnému 20000 ms CPU základna náklady.

Zde jsou tyto výpočty na CPU času, který jsem použil pro výše uvedené analýzy:

     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
  • kolo je čas CPU na zpáteční let zpět datetime.

  • jedno je CPU času pro jednu konverzi na alternativní typ dat (ten, který má vedlejší účinek odstranění časový úsek).

  • základna je výpočet odečtením singlerozdílu mezi oběma vyvolání: single - (round - single). To je přibližný údaj, který předpokládá, že převod do a z tohoto datového typu, a datetimeje přibližně stejná v obou směrech. Zdá se, že tento předpoklad není dokonalá, ale je blízko, protože hodnoty jsou v blízkosti 20000 ms s jedinou výjimkou.

A ještě jedna zajímavá věc je, že základní cena je téměř stejná jako jedinou Convert(date)metodou (která má být téměř 0 náklady, protože server může interně extrahovat denní část celého čísla přímo z prvních čtyř bajtů datetimedatového typu).

Závěr

Tak, jak to vypadá, že jeden směr varcharzpůsob konverze trvá cca 1,8 us a jediný směr DateDiffmetoda trvá asi 0,18 us. Jsem založit to na nejkonzervativnější „base CPU“ čas v mém testování celkem 18458 ms pro 25,920,000 řadách, takže 23218 MS / 25920000 = 0,18 us. Zdánlivý 10x zlepšení se zdá jako hodně, ale to je upřímně řečeno docela malý, dokud máte co do činění s stovky tisíc řádků (617k řádky = 1 sekunda spoření).

Dokonce i vzhledem k tomu, tento malý absolutní zlepšení, podle mého názoru je DateAddzpůsob vyhraje, protože to je nejlepší kombinaci výkonu a jasnosti. Odpověď, která vyžaduje „magické číslo“ ze 0.50000004se chystá kousnout někoho nějaký den (pět nuly nebo šest ???), a navíc je to těžší pochopit.

Další poznámky

Když jsem se dostat nějakou dobu budu změní 0.50000004na '12:00:00.003'a uvidíme, jak to dělá. To je přeměněno na stejnou datetimehodnotu, a považuji za mnohem jednodušší si pamatovat.

Pro zájemce, výše uvedené testy byly prováděny na serveru, kde @@ Version vrátí následující:

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 v systému Windows NT 5.2 (Build 3790: Service Pack 2)

Odpovězeno 12/09/2010 v 23:57
zdroj uživatelem

hlasů
27

SQL Server 2008 má nové datum datový typ , což zjednodušuje tento problém:

SELECT CAST(CAST(GETDATE() AS date) AS datetime)
Odpovězeno 06/08/2008 v 07:44
zdroj uživatelem

hlasů
16

Itzik Ben-Gan v Datetime výpočty, Part 1 (SQL Server Magazine, únor 2007) ukazuje tři způsoby provedení takového převodu ( nejpomalejší po nejrychlejší , rozdíl mezi druhým a třetím způsobu je malý):

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)

Vaše technika (lití float ) je navrženo čtečkou v dubnovém čísle časopisu. Podle něj má výkon srovnatelný s druhým techniky popsaná výše.

Odpovězeno 06/08/2008 v 09:06
zdroj uživatelem

hlasů
11

Your CAST- FLOOR- CASTuž se zdá být optimálním způsobem, alespoň na MS SQL Server 2005.

Některá jiná řešení, které jsem viděl, mají řetězce konverzi jako Select Convert(varchar(11), getdate(),101)v nich, což je pomalejší o faktor 10.

Odpovězeno 05/08/2008 v 21:12
zdroj uživatelem

hlasů
3

Zkuste to prosím:

SELECT CONVERT(VARCHAR(10),[YOUR COLUMN NAME],105) [YOURTABLENAME]
Odpovězeno 29/06/2013 v 10:49
zdroj uživatelem

hlasů
0

SQL2005: Doporučuji cast místo DateAdd. Například,

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

v průměru kolem 10% rychleji na mé datové sadě, než

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

(A lití do smalldatetime byl rychlejší ještě)

Odpovězeno 05/11/2014 v 04:26
zdroj uživatelem

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