Jak usunąć część czasową wartości datetime (SQL Server)?

SQL Server 2008 i up

W SQL Server 2008 i w górę, oczywiście najszybszym sposobem jest Convert(date, @date). W razie potrzeby można to cofnąć do datetime lub datetime2.

Co jest naprawdę najlepsze w SQL Server 2005 i starszych?

Widziałem niespójne twierdzenia o tym, co jest najszybsze do skrócenia czasu od daty w SQL Server, a niektórzy ludzie nawet powiedzieli, że testowali, ale moje doświadczenie było inne. Więc zróbmy bardziej rygorystyczne testy i niech każdy ma scenariusz, więc jeśli popełnię jakieś błędy, ludzie mogą mnie poprawić.

Konwersje Float Nie Są Dokładne

Po pierwsze, trzymałbym się z dala od konwersji datetime na float, ponieważ nie konwertuje się poprawnie. Możesz ujść na sucho z dokładnym usuwaniem czasu, ale myślę, że używanie go jest złym pomysłem, ponieważ w domyśle komunikuje programistom, że jest to bezpieczna operacja, a nie jest. Zobacz też:

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 nie jest coś, czego powinniśmy uczyć ludzi w naszym kodzie lub w naszych przykładach online.

Proof-Performance Testing

Jeśli chcesz wykonać kilka testów samodzielnie, aby zobaczyć, jak różne metody naprawdę się układają, będziesz potrzebował tego skryptu instalacyjnego, aby uruchomić testy dalej w dół:

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

Należy pamiętać, że tworzy to tabelę 427,57 MB w bazie danych i zajmie około 15-30 minut. Jeśli twoja baza danych jest mała i ustawiona na 10% wzrost zajmie to dłużej niż w przypadku, gdy najpierw będziesz wystarczająco duży.

Teraz skrypt testujący wydajność. Należy pamiętać, że celowe jest nie zwracanie wierszy z powrotem do klienta, ponieważ jest to szalenie kosztowne w przypadku 26 milionów wierszy i ukryłoby różnice wydajności między metodami.

Wyniki Wydajności

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;

Niektóre Bełkotliwe Analizy

Kilka uwag na ten temat. Po pierwsze, jeśli tylko wykonując grupę przez lub porównanie, nie ma potrzeby konwersji z powrotem do datetime. Możesz więc zaoszczędzić trochę procesora, unikając tego, chyba że potrzebujesz ostatecznej wartości do celów wyświetlania. Można nawet pogrupować według wartości niekonwertowanej i umieścić konwersję tylko w 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)

Zobacz też, jak konwersje liczbowe zajmują nieco więcej czasu, aby przekształcić je z powrotem na datetime, ale konwersja varchar prawie podwaja się? Ujawnia to część procesora, która jest poświęcona obliczeniu daty w zapytaniach. Istnieją części użycia procesora, które nie wymagają obliczania daty, a to wydaje się być czymś zbliżonym do 19875 ms w powyższych zapytaniach. Następnie konwersja pobiera dodatkową kwotę, więc jeśli są dwie konwersje, kwota ta jest zużywana około dwa razy.

Więcej badań ujawnia, że w porównaniu do Convert(, 112), zapytanie Convert(, 101) ma jakiś dodatkowy wydatek CPU (ponieważ używa dłuższego varchar?), ponieważ druga konwersja z powrotem do date nie kosztuje tyle jako początkową konwersję na varchar, ale z Convert(, 112) jest bliżej tego samego kosztu bazowego procesora 20000 ms.

Oto te obliczenia na czas procesora, które użyłem do powyższej analizy:

     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

• round to czas procesora na podróż powrotną do datetime.

• single to czas procesora dla pojedynczej konwersji na alternatywny typ danych (ten, który ma efekt uboczny usunięcia czasu porcja).

• baza {[86] } jest obliczeniem odejmowania od single różnicy między dwoma wywołaniami: single - (round - single). Jest to liczba, która zakłada konwersję do i z tego typu danych i datetime jest w przybliżeniu taka sama w obu kierunkach. Wydaje się, że to założenie nie jest doskonałe, ale jest bliskie, ponieważ wszystkie wartości są bliskie 20000 ms z tylko jednym wyjątkiem.

Wniosek

Wygląda więc na to, że konwersja jednokierunkowa varchar zajmuje około 1,8 µs, A jednokierunkowa DateDiff około 0,18 µs. Bazuję na najbardziej konserwatywnym czasie "bazowego procesora" w moich testach 18458 ms dla 25,920,000 wierszy, czyli 23218 ms / 25920000 = 0,18 µs. Pozorna poprawa 10x wydaje się dużo, ale jest szczerze mówiąc dość mała, dopóki nie masz do czynienia z setkami tysięcy wierszy (wiersze 617k = oszczędności 1 sekundy).

Nawet biorąc pod uwagę tę niewielką absolutną poprawę, moim zdaniem metoda DateAdd wygrywa, ponieważ jest to najlepsze połączenie wydajności i przejrzystości. Odpowiedź, która wymaga "magicznej liczby" 0.50000004, kiedyś kogoś ugryzie(pięć zer czy sześć???), dodatkowo trudniej jest Rozumiem.

Uwagi Dodatkowe

Kiedy będę miał trochę czasu, zmienię 0.50000004 na {[30] } i zobaczę, jak to będzie. Jest przekonwertowana na tę samą wartość datetime i uważam, że jest o wiele łatwiejsza do zapamiętania.

Dla zainteresowanych powyższe testy zostały uruchomione na serwerze, na którym @ @ Version zwraca:

Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) 09 lipca 2008 14:43:34 Copyright (c) 1988-2008 Microsoft Corporation Standard Wersja na Windows NT 5.2 (Build 3790: Service Pack 2)