Блог

Мне нужно извлекать строки между определенными датами по тихоокеанскому стандартному времени, но в базе данных столбец даты хранится как UTC. В настоящее время я выполняю преобразование в предложении WHERE, но это занимает много времени, поскольку я использую функцию в предложении WHERE, и это вызывает полное сканирование таблицы.

 SELECT T.foo
FROM table T
JOIN .... ON ....
WHERE CONVERT(DATETIME,SWITCHOFFSET(T.start_date_utc,DATEPART(TZOFFSET, T.start_date_utc AT TIME ZONE 'Pacific Standard Time'))) BETWEEN '1/1/2018 00:00:00 AM' AND '2/28/2018 23:59:59 PM'
  

Есть ли лучший способ сделать это без функции преобразования в предложении WHERE?

• Запрос занимает 30 минут, но когда я удаляю CONVERT и просто WHERE BETWEEN date1 AND date2 выполняю его, он выполняется намного быстрее, за 21 секунду.
• Другие предложения приветствуются, конечно, если есть лучшее решение, чем сравнение даты с оператором преобразования.

Другая информация:

• У меня есть только CONNECT SQL VIEW ANY DATABASE разрешения и, поэтому я не могу создавать новые таблицы, индексы и т. Д.

• Без SHOWPLAN разрешений я не вижу план выполнения запроса, чтобы проверить, не является ли что-то еще причиной медленного запроса. Но поскольку удаление инструкции convert увеличивает время с 30 минут до 21 секунды, я подозреваю, что это та строка.

• Я использую Microsoft SQL Server Management Studio 17

• Windows 7

Что я пробовал:

• Я попытался создать общую таблицу и преобразовать столбец даты перед основным запросом, но это все так же медленно. Вероятно, поскольку преобразование все еще должно происходить в каждой строке.

  WITH CTE AS (
SELECT T.Foo,
CONVERT(DATETIME,SWITCHOFFSET(T.start_date_utc, DATEPART(TZOFFSET, T.start_date_utc AT TIME ZONE 'Pacific Standard Time'))) as start_date_pst )

Другой подход заключается в изменении вашего ввода на UTC. Это одноразовая операция, и SQL легче фильтровать. Ниже приведены различные способы достижения той же цели в зависимости от вашей версии sql.

 -- 1
WHERE T.start_date_utc
     BETWEEN '1/1/2018 08:00:00 AM' /*PST to UTC*/
        AND '2/28/2018 07:59:59 PM' /*PST to UTC*/
-- 2    
WHERE T.start_date_utc
 BETWEEN DATEADD(HOUR, 8, '1/1/2018 00:00:00 AM'),  /*PST to UTC*/
    AND DATEADD(HOUR, 8, '2/28/2018 23:59:59 PM' /*PST to UTC*/

-- 3
WHERE T.start_date_utc
     BETWEEN SWITCHOFFSET('1/1/2018 00:00:00 AM', '-08:00') /*PST to UTC*/
        AND SWITCHOFFSET('2/28/2018 23:59:59 PM', '-08:00') /*PST to UTC*/


/*Avoiding hard coded values*/

-- 4
DECLARE @offset INT

 SELECT @offset = DATEPART(TZOFFSET, CONVERT(datetime,'1/1/2018 00:00:00 AM') AT TIME ZONE 'Pacific Standard Time') * -1

 WHERE T.start_date_utc
 BETWEEN DATEADD(MINUTE, @offset, '1/1/2018 00:00:00 AM'),  /*PST to UTC*/
    AND DATEADD(MINUTE, @offset, '2/28/2018 23:59:59 PM' /*PST to UTC*/

-- 5        
WHERE T.start_date_utc
     BETWEEN CONVERT(datetime,'1/1/2018 00:00:00 AM')  AT TIME ZONE 'Pacific Standard Time' /*PST to UTC*/
        AND CONVERT(datetime,'2/28/2018 23:59:59 PM')  AT TIME ZONE 'Pacific Standard Time' /*PST to UTC*/
  

Прямо сейчас вы выполняете преобразование часовых поясов для всех ваших данных. Вместо этого выясните, какими должны быть правильные временные метки UTC один раз, а затем сравните свои данные с этим. Вот так:

 declare @startTimePST datetime = '1/1/2018 00:00:00 AM',
    @endTimePST datetime = '2/28/2018 23:59:59 PM';

declare @startTimeUTC datetime = @startTimePST 
    at time zone 'Pacific Standard Time' 
    at time zone 'UTC',
  @endTimeUTC datetime = @endTimePST 
    at time zone 'Pacific Standard Time' 
    at time zone 'UTC';

select @startTimePST, @startTimeUTC, 
    @endTimePST, @endTimeUTC

SELECT T.foo
FROM table T
JOIN .... ON ....
WHERE T.start_date_utc BETWEEN @startTimeUTC AND @endTimeUTC;
  

В качестве пояснения я объявляю переменные и присваиваю им значения, которые представляют ваши временные метки PST. Затем я преобразую их в UTC путем двойного использования at time zone предложения. Почему два? Первый из них присоединяет часовой пояс PST к вашим временным меткам без часового пояса, а затем преобразует их в UTC. Как только у меня будут эти конечные точки, я смогу использовать их непосредственно в запросе.