SQL Server 如何避免 WHERE 子句中的时区转换

Question

>我必须检索太平洋标准时间某些日期之间的行，但数据库将日期列存储为 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 秒，我怀疑是那条线。

• 我正在使用 SQL Server Management Studio 17 Microsoft

• 视窗 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)

Answer 1

另一种方法是将输入更改为 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*/

Answer 2

现在，您正在针对所有数据进行时区转换。相反，找出正确的 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 时间戳的值。然后，我通过双重使用at time zone子句将它们转换为 UTC。为什么是两个？第一个将 PST 时区附加到无时区时间戳，然后将其转换为 UTC。拥有这些终结点后，我可以直接在查询中使用它们。