Jak usunąć część czasu z wartości daty i godziny (SQL Server)?

Oto czego używam:

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

Myślę, że może być lepszy i bardziej elegancki sposób.

Wymagania:

• Musi być jak najszybszy (im mniej rzuca, tym lepiej).
• Wynik końcowy musi być datetimetypem, a nie łańcuchem.

SQL Server 2008 i nowsze

W SQL Server 2008 i nowszych oczywiście najszybszym sposobem jest Convert(date, @date). Można to odrzucić z powrotem do datetimelub, datetime2jeśli to konieczne.

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

Widziałem niespójne stwierdzenia dotyczące najszybszego skrócenia czasu od daty w SQL Server, a niektórzy nawet powiedzieli, że przeprowadzili testy, ale moje doświadczenie było inne. Zróbmy więc bardziej rygorystyczne testy i pozwólmy każdemu mieć skrypt, więc jeśli popełnię jakieś błędy, ludzie będą mogli mnie poprawić.

Konwersje zmiennoprzecinkowe nie są dokładne

Po pierwsze, nie chciałbym konwertować datetimena float, ponieważ nie konwertuje poprawnie. Możesz uciec od dokładnego usunięcia czasu, ale myślę, że używanie go jest złym pomysłem, ponieważ pośrednio komunikuje programistom, że jest to operacja bezpieczna, a tak nie jest . Spójrz:

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.

Nie jest to nawet najszybszy sposób!

Dowód - testowanie wydajności

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

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 spowoduje to utworzenie tabeli o wielkości 427,57 MB w Twojej bazie danych i zajmie około 15-30 minut. Jeśli Twoja baza danych jest mała i ustawiona na 10% wzrostu, zajmie to więcej czasu niż w przypadku wystarczająco dużego rozmiaru.

Teraz czas na rzeczywisty skrypt do testowania wydajności. Należy pamiętać, że celowe jest, aby nie zwracać wierszy z powrotem do klienta, ponieważ jest to szalenie drogie w przypadku 26 milionów wierszy i mogłoby ukryć różnice w 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;

Trochę wędrującej analizy

Kilka uwag na ten temat. Po pierwsze, jeśli wykonujesz tylko GROUP BY lub porównanie, nie ma potrzeby powrotu 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żesz nawet GROUP BY nieprzekonwertowaną wartość 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ż, że konwersja z powrotem do konwersji numerycznej zajmuje tylko nieco więcej czasu datetime, ale varcharkonwersja prawie się podwaja? Ujawnia to część procesora przeznaczoną na obliczanie daty w zapytaniach. Istnieją części wykorzystania procesora, które nie obejmują obliczania daty i wydaje się, że w powyższych zapytaniach jest to coś zbliżonego do 19875 ms. Wtedy konwersja wymaga dodatkowej kwoty, więc jeśli są dwie konwersje, ta kwota jest zużyta około dwa razy.

Więcej badań ujawnia, że ​​w porównaniu Convert(, 112)z Convert(, 101)zapytaniem jest dodatkowy koszt procesora (ponieważ zużywa dłużej varchar?), Ponieważ druga konwersja z powrotem do datenie kosztuje tyle, co początkowa konwersja do varchar, ale z Convert(, 112)nią jest bliższa tym samym 20000 ms podstawowy koszt procesora.

Oto te obliczenia czasu procesora, których 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óż w obie strony do datetime.

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

• Podstawa jest obliczenie odjęcie od singleróżnicy pomiędzy dwoma wywołaniami: single - (round - single). Jest to liczba, która zakłada konwersję do iz tego typu danych i datetimejest 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 jednym tylko wyjątkiem.

Jeszcze jedną interesującą rzeczą jest to, że koszt podstawowy jest prawie równy kosztowi pojedynczej Convert(date)metody (który musi być prawie zerowy, ponieważ serwer może wewnętrznie wyodrębnić część całkowitą dnia z pierwszych czterech bajtów datetimetypu danych).

Wniosek

Wygląda więc na to, że varcharmetoda konwersji jednokierunkowej zajmuje około 1,8 μs, a DateDiffmetoda jednokierunkowa około 0,18 μs. Opieram to na najbardziej konserwatywnym czasie „bazowego procesora” w moich testach, który wynosi łącznie 18458 ms dla 25 920 000 wierszy, czyli 23218 ms / 25920000 = 0,18 μs. Pozorna 10-krotna poprawa wydaje się duża, ale szczerze mówiąc jest dość niewielka, dopóki nie masz do czynienia z setkami tysięcy wierszy (617 tys. Wierszy = 1 sekunda oszczędności).

Nawet biorąc pod uwagę tę niewielką absolutną poprawę, moim zdaniem DateAddmetoda wygrywa, ponieważ jest najlepszym połączeniem wydajności i przejrzystości. Odpowiedź, która wymaga „magicznej liczby”, 0.50000004pewnego dnia kogoś ugryzie (pięć zer lub sześć ???), a ponadto będzie trudniejsza do zrozumienia.

Dodatkowe uwagi

Kiedy będę miał trochę czasu, zmienię 0.50000004się '12:00:00.003'i zobaczę, jak to działa. Jest konwertowany na tę samą datetimewartość i uważam, że jest znacznie łatwiejszy 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) 9 lipca 2008 14:43:34 Copyright (c) 1988-2008 Microsoft Corporation Standard Edition w systemie Windows NT 5.2 (kompilacja 3790: dodatek Service Pack 2)

SQL Server 2008 ma nowy typ danych daty, co upraszcza ten problem:

SELECT CAST(CAST(GETDATE() AS date) AS datetime)

Itzik Ben-Gan w DATETIME Calculations, Part 1 (SQL Server Magazine, luty 2007) przedstawia trzy metody wykonywania takiej konwersji (od najwolniejszej do najszybszej ; różnica między drugą a trzecią metodą jest niewielka):

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)

Twoja technika (casting to float ) jest sugerowana przez czytelnika w kwietniowym numerze magazynu. Według niego ma wydajność porównywalną z przedstawioną powyżej drugą techniką.

Twój CAST- FLOOR- CASTjuż wydaje się być optymalnym sposobem, przynajmniej na MS SQL Server 2005.

Niektóre inne rozwiązania, które widziałem, mają konwersję ciągów, jak Select Convert(varchar(11), getdate(),101)w nich, która jest wolniejsza o współczynnik 10.

Proszę spróbować:

SELECT CONVERT(VARCHAR(10),[YOUR COLUMN NAME],105) [YOURTABLENAME]

SQL2005: Zalecam rzutowanie zamiast dateadd. Na przykład,

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

średnio około 10% szybciej w moim zbiorze danych niż

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

(a rzucanie na mały czas było jeszcze szybsze)