Как удалить временную часть значения datetime (SQL Server)?

SQL Server 2008 и выше

в SQL Server 2008 и выше, конечно, самый быстрый способ Convert(date, @date). Это может быть отброшено назад к datetime или datetime2 при необходимости.

что действительно лучше в SQL Server 2005 и старше?

я видел противоречивые утверждения о том, что быстрее всего усекать время от даты в SQL Server, и некоторые люди даже сказали, что они проводили тестирование, но мой опыт был другим. Так что давайте сделайте более строгое тестирование и пусть у всех есть сценарий, чтобы, если я сделаю какие-либо ошибки, люди могли меня исправить.

Float Преобразования Не Являются Точными

во-первых, я бы держался подальше от преобразования datetime до float, потому что он не преобразует правильно. Вам может сойти с рук точно выполнять удаление времени, но я думаю, что это плохая идея использовать его, потому что он неявно сообщает разработчикам, что это безопасная работа и это не. Взгляните:

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

это не то, что мы должны учить людей в нашем коде или в наших примерах онлайн.

кроме того, это даже не самый быстрый способ!

Доказательство – Тестирование Производительности

если вы хотите выполнить некоторые тесты самостоятельно, чтобы увидеть, как различные методы действительно складываются, то вам понадобится этот сценарий установки для запуска тестов дальше вниз:

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

обратите внимание, что это создает таблицу 427.57 MB В вашей базе данных и займет примерно 15-30 минут для запуска. Если ваша база данных мала и установлена на 10% роста это займет больше времени, чем если Вы размер достаточно большой в первую очередь.

теперь для фактического сценария тестирования производительности. Обратите внимание, что целесообразнее не возвращать строки обратно клиенту, так как это безумно дорого на 26 миллионов строк и скроет различия в производительности между методы.

Результаты

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;

Какой-То Бессвязный Анализ

некоторые заметки об этом. Прежде всего, если просто выполнить группу или сравнение, нет необходимости конвертировать обратно в datetime. Таким образом, вы можете сохранить некоторый процессор, избегая этого, если вам не нужно окончательное значение для целей отображения. Вы даже можете сгруппировать по необращенному значению и поместить преобразование только в 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)

кроме того, смотрите, как числовые преобразования занимают немного больше времени, чтобы преобразовать обратно в datetime, а varchar преобразование почти удваивается? Это показывает часть ЦП, которая посвящена вычислению даты в запросах. Есть части использования ЦП, которые не связаны с вычислением даты, и это, по-видимому, что-то близкое к 19875 МС в приведенных выше запросах. Затем преобразование занимает некоторую дополнительную сумму, поэтому, если есть два преобразования, это сумма израсходована примерно в два раза.

больше экзаменов показывает, что по сравнению с Convert(, 112) на Convert(, 101) запрос имеет некоторые дополнительные расходы на процессор (так как он использует более длинный varchar?), потому что второе преобразование обратно в date не стоит столько, сколько первоначальное преобразование в varchar, но с Convert(, 112) это ближе к той же 20000 МС базовой стоимости процессора.

вот те вычисления на процессорном времени, которые я использовал для выше анализ:

     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

• круглые это процессорное время для обратной поездки в datetime.

• single это процессорное время для одного преобразования в альтернативный тип данных (тот, который имеет побочный эффект удаления части времени).

• базовый это расчет вычитания из single разница между двумя вызовами:single - (round - single). Это примерная цифра, которая предполагает преобразование в и из этого типа данных и datetime примерно то же самое в любом направлении. Похоже, это предположение не идеально, но близко, потому что все значения близки к 20000 МС только с одним исключением.

еще одна интересная вещь заключается в том, что базовая стоимость почти равна единице Convert(date) метод (который должен быть почти 0 затрат, так как сервер может внутренне извлечь целую дневную порцию прямо из первые четыре байта datetime тип данных).

вывод

так что это похоже на то, что однонаправленный varchar метод преобразования занимает около 1,8 МКС и однонаправленный DateDiff метод занимает около 0,18 МКС. Я основываю это на самом консервативном времени "базового процессора" в моем тестировании 18458 МС всего для 25 920 000 строк, поэтому 23218 МС / 25920000 = 0,18 МКС. Очевидное улучшение 10x кажется большим, но это, честно говоря, довольно мало пока вы имеете дело с сотнями тысяч строк (617k строк = 1 секунда экономии).

даже учитывая это небольшое абсолютное улучшение, на мой взгляд,DateAdd метод выигрывает, потому что это лучшее сочетание производительности и ясности. Ответ, который требует "магическое число"0.50000004 собирается укусить кого-нибудь (пять нулей или шесть???), плюс это сложнее понять.

Дополнительная Информация

когда я получаю некоторое время Я собираюсь изменить 0.50000004 до '12:00:00.003' и посмотреть, как он делает. Он преобразуется в то же самое datetime значение и я считаю, что это гораздо легче запомнить.

для тех, кто заинтересован, вышеуказанные тесты были запущены на сервере, где @ @ Version возвращает следующее:

Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) Jul 9 2008 14: 43: 34 Copyright (c) 1988-2008 Microsoft Corporation Standard Edition на Windows NT 5.2 (сборка 3790: пакет обновления 2)