Какой хороший способ проверить, находятся ли два datetime в одном календарном дне в TSQL?

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

В этом случае объединение кода функции обратно в запрос кажется нецелесообразным.

Я думаю, что мне здесь не хватает чего-то простого.

Вот функция для справки.

if not exists (select * from dbo.sysobjects 
              where id = object_id(N'dbo.f_MakeDate') and               
              type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
  exec('create function dbo.f_MakeDate() returns int as 
         begin declare @retval int return @retval end')
go

alter function dbo.f_MakeDate
(
    @Day datetime, 
    @Hour int, 
    @Minute int
)
returns datetime
as

/*

Creates a datetime using the year-month-day portion of @Day, and the 
@Hour and @Minute provided

*/

begin

declare @retval datetime
set @retval = cast(
    cast(datepart(m, @Day) as varchar(2)) + 
    '/' + 
    cast(datepart(d, @Day) as varchar(2)) + 
    '/' + 
    cast(datepart(yyyy, @Day) as varchar(4)) + 
    ' ' + 
    cast(@Hour as varchar(2)) + 
    ':' + 
    cast(@Minute as varchar(2)) as datetime)
return @retval
end

go

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

where 
dbo.f_MakeDate(dateadd(hh, tz.Offset + 
    case when ds.LocalTimeZone is not null 
    then 1 else 0 end, t.TheDateINeedToCheck), 0, 0) = @activityDateMidnight

[Редактировать]

Я включил предложение @ Тодда:

where datediff(day, dateadd(hh, tz.Offset + 
    case when ds.LocalTimeZone is not null 
    then 1 else 0 end, t.TheDateINeedToCheck), @ActivityDate) = 0

Мое неправильное представление о том, как работает datediff (один и тот же день года в последовательных годах дает 366, а не 0, как я ожидал), заставило меня тратить много сил.

Но план запроса не изменился. Я думаю, что мне нужно вернуться к чертежной доске со всем этим.

22.08.2008 14:53:47
Пожалуйста, посмотрите ответ Марка Брэкетта, который является правильным . Я понимаю, что вашему вопросу уже 2 года, но, пожалуйста, давайте не будем вводить людей на неверный путь, которые посещают этот вопрос. Ответ Тодда Тингена работает, но это ужасная производительность, как вы нашли в то время!
ErikE 12.09.2010 22:47:50
Ответ Марка для этой ситуации верный, потому что от него зависит оптимизатор. Использование dateadd, как я это сделал, - простой и сжатый способ узнать, относятся ли две даты к одному календарному дню, который был первоначальным вопросом.
Todd 15.12.2010 03:30:24
10 ОТВЕТОВ
РЕШЕНИЕ

Это гораздо более кратко:

where 
  datediff(day, date1, date2) = 0
68
22.08.2008 15:02:46
Почему это имеет 9 голосов "за" и получил награду? Это сжато, хорошо, и привело ОП на неверный путь. Поскольку вторая дата, @ActivityDate, исправлена, мы можем переместить математику в правую сторону и получить гораздо лучшую производительность.
ErikE 12.09.2010 22:44:10
Согласился с @emtucifor. Эта логика не может быть использована сервером sql.
DForck42 13.09.2010 14:32:09
@ErikE, я не совсем понимаю, что вы имеете в виду, у вас есть фрагмент кода?
Joyce 25.08.2017 17:51:33
@ Джойс, посмотри на ответ Марка Брэкетта . Он поставлен MyDateTimeна одну сторону любого математического оператора, сам по себе . Это означает, что механизм запросов может выполнить поиск диапазона и перейти непосредственно в b-дереве к строкам, которые находятся между этими двумя временами. Принимая во внимание этот ужасный, ужасный, ужасный совет, каждая строка в таблице должна иметь математику, которая является сканированием и ужасна для производительности. Если в таблице 100 миллионов строк и только 10 строк соответствуют критерию, подумайте, как плохо смотреть на все из них, чтобы найти 10 !!!!!!
ErikE 25.08.2017 19:50:01
where
year(date1) = year(date2)
and month(date1) = month(date2)
and day(date1) = day(date2)
5
22.08.2008 14:56:16

это удалит компонент времени из даты для вас:

select dateadd(d, datediff(d, 0, current_timestamp), 0)
2
22.08.2008 15:06:59

Вы в значительной степени должны держать левую часть предложения where в чистоте. Итак, обычно вы делаете что-то вроде:

WHERE MyDateTime >= @activityDateMidnight 
      AND MyDateTime < (@activityDateMidnight + 1)

(Некоторые люди предпочитают DATEADD (d, 1, @activityDateMidnight) вместо этого - но это то же самое).

Хотя таблица TimeZone немного усложняет дело. Это немного неясно из вашего фрагмента, но похоже, что t.TheDateInTable находится в GMT с идентификатором часового пояса, и что вы затем добавляете смещение для сравнения с @activityDateMidnight - по местному времени. Я не уверен, что такое ds.LocalTimeZone.

Если это так, то вместо этого вам нужно перевести @activityDateMidnight в GMT.

19
22.08.2008 15:16:00

Убедитесь, что вы читаете только в базе данных, можете ли вы получить улучшение на 1000%, изменив несколько строк кода, чтобы вы были уверены, что оптимизатор может эффективно использовать индекс при работе с датами

4
22.08.2008 15:21:21

Вы избалованы выбором с точки зрения вариантов здесь. Если вы используете Sybase или SQL Server 2008, вы можете создавать переменные типа date и присваивать им значения datetime. Механизм базы данных избавляет вас от времени. Вот быстрый и грязный тест для иллюстрации (код на диалекте Sybase):

declare @date1 date
declare @date2 date
set @date1='2008-1-1 10:00'
set @date2='2008-1-1 22:00'
if @date1=@date2
    print 'Equal'
else
    print 'Not equal'

Для SQL 2005 и более ранних версий вы можете преобразовать дату в varchar в формате, в котором нет компонента времени. Например, следующее возвращает 2008.08.22

select convert(varchar,'2008-08-22 18:11:14.133',102)

Часть 102 определяет форматирование (книги в Интернете могут перечислить для вас все доступные форматы)

Итак, вы можете написать функцию, которая берет datetime, извлекает элемент date и отбрасывает время. Вот так:

create function MakeDate (@InputDate datetime) returns datetime as
begin
    return cast(convert(varchar,@InputDate,102) as datetime);
end

Затем вы можете использовать функцию для компаньонов

Select * from Orders where dbo.MakeDate(OrderDate) = dbo.MakeDate(DeliveryDate)
1
22.08.2008 15:22:51

Я бы использовал функцию dayofyear в datepart:


Select *
from mytable
where datepart(dy,date1) = datepart(dy,date2)
and
year(date1) = year(date2) --assuming you want the same year too

Смотрите ссылку на часть даты здесь .

0
22.08.2008 15:27:02

Что касается часовых поясов, еще одна причина хранить все даты в одном часовом поясе (предпочтительно UTC). В любом случае, я думаю, что ответы с использованием datediff, datepart и различных встроенных функций даты - ваш лучший выбор.

0
22.08.2008 15:31:35

Эрик З Борода:

Я храню все даты в GMT. Вот пример использования: что-то произошло в 23:00 EST 1-го, то есть 2-го GMT. Я хочу увидеть активность для 1-го, и я в EST, поэтому я хочу видеть активность в 11 вечера. Если бы я только сравнил сырые даты по Гринвичу, я бы пропустил некоторые вещи. Каждая строка в отчете может представлять деятельность из другого часового пояса.

Правильно, но когда вы говорите, что интересуетесь активностью 1 января 2008 года, EST:

SELECT @activityDateMidnight = '1/1/2008', @activityDateTZ = 'EST'

вам просто нужно преобразовать это в GMT (я игнорирую сложность запроса за день до того, как EST перейдет в EDT, или наоборот):

Table: TimeZone
Fields: TimeZone, Offset
Values: EST, -4

--Multiply by -1, since we're converting EST to GMT.
--Offsets are to go from GMT to EST.
SELECT @activityGmtBegin = DATEADD(hh, Offset * -1, @activityDateMidnight)
FROM TimeZone
WHERE TimeZone = @activityDateTZ

который должен дать вам «01.01.2008 4:00 AM». Затем вы можете просто искать в GMT:

SELECT * FROM EventTable
WHERE 
   EventTime >= @activityGmtBegin --1/1/2008 4:00 AM
   AND EventTime < (@activityGmtBegin + 1) --1/2/2008 4:00 AM

Данное событие сохраняется с GMTTimeTimeTime 1/2/2008 3:00 AM. Вам даже не нужен часовой пояс в EventTable (по крайней мере, для этой цели).

Поскольку EventTime отсутствует в функции, это прямое сканирование индекса, которое должно быть довольно эффективным. Сделайте EventTime вашим кластерным индексом, и он будет летать. ;)

Лично я бы заставил приложение преобразовать время поиска в GMT перед выполнением запроса.

2
22.08.2008 16:33:13

Эрик З Борода:

дата активности должна указывать местный часовой пояс, но не конкретный

Хорошо - вернемся к чертежной доске. Попробуй это:

where t.TheDateINeedToCheck BETWEEN (
    dateadd(hh, (tz.Offset + ISNULL(ds.LocalTimeZone, 0)) * -1, @ActivityDate)
    AND
    dateadd(hh, (tz.Offset + ISNULL(ds.LocalTimeZone, 0)) * -1, (@ActivityDate + 1))
)

который переведет @ActivityDate по местному времени и сравнит с этим. Это ваш лучший шанс для использования индекса, хотя я не уверен, что он будет работать - вы должны попробовать его и проверить план запроса.

Следующим вариантом будет индексированное представление с индексированным вычисленным TimeINeedToCheck по местному времени . Тогда вы просто возвращаетесь к:

where v.TheLocalDateINeedToCheck BETWEEN @ActivityDate AND (@ActivityDate + 1)

который определенно использовал бы индекс - хотя у вас есть небольшие накладные расходы на INSERT и UPDATE.

1
23.08.2008 02:14:08