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

Вот что я использую:

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

Я думаю, что может быть лучший и элегантный способ.

Требования:

Это должно быть как можно быстрее (чем меньше кастинг, тем лучше).
Конечным результатом должен быть тип datetime, а не строка.

Ответ 1

SQL Server 2008 и

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

Что на самом деле лучше всего в SQL Server 2005 и старше?

Я видел противоречивые утверждения о том, что самое быстрое для сокращения времени с даты на SQL Server, а некоторые даже говорят, что они тестировали, но мой опыт был другим. Поэтому давайте сделаем еще более строгий тест, и пусть у всех есть script, поэтому, если я ошибаюсь, люди могут меня поправить.

Преобразования с плавающей точкой не являются точными

Во-первых, я бы держался подальше от преобразования 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

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

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

Проверка эффективности -

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

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%, это займет больше времени, чем если бы вы сначала стали большими.

Теперь для фактического тестирования производительности script. Имейте в виду, что целесообразна не возвращать строки обратно клиенту, так как это безумие стоит 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;

Пример анализа Rambling

Некоторые заметки об этом. Прежде всего, если просто выполнить GROUP BY или сравнение, нет необходимости конвертировать обратно в datetime. Таким образом, вы можете сэкономить некоторый процессор, избегая этого, если только вам не понадобится окончательное значение для целей показа. Вы можете даже GROUP BY неотвержденным значением и поместить преобразование только в предложение 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 почти удваивается? Это показывает часть CPU, которая посвящена вычислению даты в запросах. Есть части использования ЦП, которые не связаны с вычислением даты, и это похоже на что-то близкое к 19875 мс в вышеуказанных запросах. Затем преобразование принимает некоторую дополнительную сумму, поэтому, если есть два преобразования, эта сумма используется примерно в два раза.

Дополнительная проверка показывает, что по сравнению с Convert(, 112) запрос Convert(, 101) имеет некоторые дополнительные затраты на процессор (поскольку он использует более длинный varchar?), поскольку второе преобразование обратно в date не так дорого как начальное преобразование в varchar, но с Convert(, 112) оно ближе к тем же базовым затратам ЦП 20000 ms.

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

     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 - это время CPU для возврата туда и обратно к datetime.
single - это процессорное время для одного преобразования в альтернативный тип данных (тот, который имеет побочный эффект удаления временной части).
base - это вычисление вычитания из single разницы между двумя вызовами: single - (round - single). Это цифра шарикового знака, которая предполагает преобразование в и из этого типа данных, а datetime примерно одинакова в любом направлении. Похоже, это предположение не является совершенным, но близко, потому что значения близки к 20000 мск с единственным исключением.

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

Заключение

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

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

Дополнительные примечания

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

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

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

Ответ 2

SQL Server 2008 имеет новый тип данных date, и это упрощает эту проблему:

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

Ответ 3

Ицик Бен-Ган в DATETIME Calculations, часть 1 (Журнал SQL Server, февраль 2007 г.) показывает три метода выполнения такого преобразования ( самый медленный до самого быстрого, разница между вторым и третьим методом мала):

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)

Ваша техника (кастинг для плавания) предлагается читателем в апрельском номере журнала. По его словам, он имеет производительность, сравнимую с характеристикой второй техники, представленной выше.

Ответ 4

Ваш CAST - FLOOR - CAST уже кажется оптимальным способом, по крайней мере, на MS SQL Server 2005.

Некоторые другие решения, которые я видел, имеют строковое преобразование, например Select Convert(varchar(11), getdate(),101) в них, которое медленнее в 10 раз.

Ответ 5

Попробуйте:

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

Ответ 6

SQL2005: Я рекомендую использовать вместо dateadd. Например,

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

в среднем на 10% быстрее на моем наборе данных, чем

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

(и литье в smalldatetime было еще быстрее)