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

голоса
77

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

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

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

Требования:

  • Он должен быть как можно быстрее (чем меньше отливки, тем лучше).
  • Конечный результат должен быть datetimeтипом, а не строка.
Задан 05/08/2008 в 21:08
источник пользователем
На других языках...                            


6 ответов

голоса
106

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 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;

Некоторые Rambling анализ

Некоторые замечания по этому поводу. Прежде всего, если только выполнение GROUP BY или сравнение, нет необходимости конвертировать обратно datetime. Таким образом , вы можете сэкономить CPU, избегая того, если вам не требуется окончательное значение для целей отображения. Вы можете даже GROUP BY превращенного значения и поставить преобразование только в ЗЕЬЕСТЕ:

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

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

Вывод

Так что, похоже , является то , что одним направлением varcharметод преобразования занимает около 1,8 мкс и однонаправленных DateDiffметоды занимают около 0,18 мкс. Я основывая это на самом консервативном «база CPU» время в моем тестировании общей сложности 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) 9 июля 2008 14:43:34 Copyright (с) 1988-2008 Корпорация Microsoft Standard Edition на Windows NT 5.2 (Build 3790: Service Pack 2)

Ответил 12/09/2010 в 23:57
источник пользователем

голоса
27

SQL Server 2008 имеет новую дату тип данных , и это упрощает эту задачу:

SELECT CAST(CAST(GETDATE() AS date) AS datetime)
Ответил 06/08/2008 в 07:44
источник пользователем

голоса
16

Ицик Бен-Ган в DATETIME Расчеты, Часть 1 (SQL Server Magazine, февраль 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)

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

Ответил 06/08/2008 в 09:06
источник пользователем

голоса
11

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

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

Ответил 05/08/2008 в 21:12
источник пользователем

голоса
3

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

SELECT CONVERT(VARCHAR(10),[YOUR COLUMN NAME],105) [YOURTABLENAME]
Ответил 29/06/2013 в 10:49
источник пользователем

голоса
0

SQL2005: Я рекомендую отбрасывать вместо DateAdd. Например,

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

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

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

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

Ответил 05/11/2014 в 04:26
источник пользователем

Cookies help us deliver our services. By using our services, you agree to our use of cookies. Learn more