Как удалить часть времени из значения datetime (SQL Server)?
Вот что я использую:
SELECT CAST(FLOOR(CAST(getdate() as FLOAT)) as DATETIME)
Я думаю, что может быть лучше и элегантнее.
Требования:
- Это должно быть как можно быстрее (чем меньше кастинг, тем лучше).
- Конечный результат должен быть
datetimeтип, а не строка.
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) метод (который должен быть почти нулевой стоимостью, так как сервер может внутренне извлечь часть целого дня прямо из первых четырех байтов 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)
SQL Server 2008 имеет новый тип данныхдаты, и это упрощает эту проблему до:
SELECT CAST(CAST(GETDATE() AS date) AS datetime)
Ицик Бен-Ган в разделе "Расчеты 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)
Ваша техника (приведение к плаванию) предложена читателем в апрельском номере журнала. По его словам, он обладает характеристиками, сопоставимыми с характеристиками второго метода, представленного выше.
Ваш CAST-FLOOR-CAST уже кажется, что оптимальный путь, по крайней мере, на MS SQL Server 2005.
Некоторые другие решения, которые я видел, имеют преобразование строк, например Select Convert(varchar(11), getdate(),101) в них, что медленнее в 10 раз.
Пожалуйста, попробуй:
SELECT CONVERT(VARCHAR(10),[YOUR COLUMN NAME],105) [YOURTABLENAME]
SQL2005: я рекомендую приведение вместо dateadd. Например,
select cast(DATEDIFF(DAY, 0, datetimefield) as datetime)
в среднем примерно на 10% быстрее по моему набору данных, чем
select DATEADD(DAY, DATEDIFF(DAY, 0, datetimefield), 0)
(и приведение в smalldatetime было еще быстрее)