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

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

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

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

Требования:

  • Это должно быть как можно быстрее (чем меньше кастинг, тем лучше).
  • Конечный результат должен быть datetimeтипом, а не строкой.
5.08.2008 20:08:38
6 ОТВЕТОВ
РЕШЕНИЕ

SQL Server 2008 и выше

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

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

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

Поплавки не точны

Во-первых, я бы избежал конвертации 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 МБ в вашей базе данных и займет около 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;

Немного азартного анализа

Несколько заметок об этом. Прежде всего, если просто выполнить 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преобразование почти удваивается? Это показывает ту часть ЦП, которая предназначена для вычисления даты в запросах. Существуют части использования ЦП, которые не связаны с вычислением даты, и в приведенных выше запросах это похоже на значение, близкое к 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
  • round - это время процессора для возврата туда и обратно 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 мкс. Кажущееся 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)

115
17.08.2017 18:18:06
+1 Кстати, на какой версии SQL Server вы это тестировали?
Martin Smith 12.09.2010 23:03:58
Похоже, у вас есть один и круглый назад в вашем столе. Кроме того, есть ли разница во времени, если вы используете charвместо varchar?
Gabe 13.09.2010 01:43:06
@ Габ, спасибо, исправлено. Char выглядит точно так же, как varchar.
ErikE 13.09.2010 01:50:22
В Oracle есть, select round(sysdate) from dualи мы определенно нуждаемся в этом в Sql Server.
Denis Valeev 13.09.2010 08:00:55
@Roman Если вы работаете с SQL Server 2008 и выше, да, преобразование в dateтип данных выполняется быстрее всего, как показано в моих тестах выше.
ErikE 30.06.2013 21:54:15

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

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

12
4.05.2012 10:02:28
Мы используем метод, предложенный Майклом Стумом в одном из наших продуктов, и он работает как шарм.
Chris Roberts 6.08.2008 07:20:50
Это совсем не оптимальный путь. Пожалуйста, смотрите мой ответ на этой же странице.
ErikE 12.09.2010 22:59:29

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

SELECT CAST(CAST(GETDATE() AS date) AS datetime)
30
13.09.2010 16:57:04
Я ошибочно ввел 0218 вместо 2018 года, и DATEADD(DATEDIFF())метод сокращения временной части вызывает исключение. Когда я datetime2select cast(CAST(convert(datetime2(0), '0218-09-12', 120) AS date) as datetime2)
Bernhard Döbler 12.09.2018 16:10:01

Ицик Бен-Ган в разделе «Расчеты DATETIME», часть 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)

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

18
24.02.2010 08:08:00
На мой взгляд, кастинг на плаву не самый лучший. Пожалуйста, смотрите мой ответ
ErikE 12.09.2010 23:48:50
@Emtucifor Я согласен, что третий метод очень неясен из-за значения 0.50000004 , но он самый быстрый, и ваши тесты это подтверждают . Таким образом, он удовлетворяет максимально быстрому требованию.
Marek Grzenkowicz 13.09.2010 08:36:08
@Emtucifor Кроме того, вот что в статье, на которую я ссылался, говорится о значении 0.50000004 : хотя это выражение короткое (и эффективное, как я скоро продемонстрирую), я должен сказать, что мне неловко с ним . Я не уверен, что могу понять, почему именно - может быть, потому что это слишком технически, и вы не можете видеть логику, связанную с датой и временем.
Marek Grzenkowicz 13.09.2010 08:37:26
Если мы собираемся использовать этот метод, я бы предпочел SELECT CAST(CAST(GETDATE() - '12:00:00.003' AS int) AS datetime)вместо этого, так как он что-то значит для меня и мне намного легче запомнить.
ErikE 13.09.2010 16:06:48
Теперь это самый быстрый в SQL 2008: Convert(date, GetDate()).
ErikE 13.06.2011 19:21:51

Пожалуйста попробуйте:

SELECT CONVERT(VARCHAR(10),[YOUR COLUMN NAME],105) [YOURTABLENAME]
4
5.04.2016 13:18:14

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

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

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

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

(и приведение в smalldatetime было еще быстрее)

1
5.11.2014 04:26:40