Sql，是否有更有效的方法来转换日期时间到不同的时区?

Question

我有一个名为AuditLogs的表，其中包含每个用户操作及其时间戳的日志。

所有这些时间戳日志都以UTC记录，时间偏移为0。而实际用户执行这些操作是在加州，使用UTC-8。

我正试图用这些数据写一份报告，所有的数据都需要在加利福尼亚时间，以便在用户执行某些操作时正确地上下文化。

这是我的查询的样子

select distinct top 1000
Users.FirstName + ' ' + Users.LastName Name
,AuditRecords.username
,AuditRecords.subtype
,convert(varchar, timestamp at time zone 'Pacific Standard Time', 0)  time
,timestamp
from 
AuditRecords 
join Users on AuditRecords.UserId = Users.Id
where 
AuditRecords.subtype <> 'log%'
and DATEPART(dy,timestamp at time zone 'Pacific Standard Time') = datepart(dy,SYSDATETIMEOFFSET() at time zone 'Pacific Standard Time')-1

关键行为

and DATEPART(dy,timestamp at time zone 'Pacific Standard Time') = datepart(dy,SYSDATETIMEOFFSET() at time zone 'Pacific Standard Time')-1

在这一行中，我试图从前一个日期检索所有记录，而不是基于滚动的24小时。例如，如果报表在11月9日运行，那么无论报表是在上午6点还是下午6点运行，它都将返回11月8日的所有记录。

问题是，如果我把这行改成没有

at time zone 'Pacific Standard Time'

在这两个地方，查询运行得快得多。但是，当使用该行将时间调整到正确的时区时，查询就会变得慢得多。

有没有更有效的方法来做这件事?

谢谢你的建议。

Answer 1

示例:

Declare @startDate datetimeoffset = dateadd(day, datediff(day, 0, sysdatetime()) - 1, 0) At Time Zone 'Pacific Standard Time'
Select @startDate
, @startDate At Time Zone 'UTC'
select distinct top 1000
Users.FirstName + ' ' + Users.LastName Name
,AuditRecords.username
,AuditRecords.subtype
,convert(varchar, timestamp at time zone 'Pacific Standard Time', 0)  time
,timestamp
from 
AuditRecords 
join Users on AuditRecords.UserId = Users.Id
where 
AuditRecords.subtype <> 'log%'
and timestamp >= @startDate At Time Zone 'UTC'
and timestamp <  dateadd(day, 1, @startDate) At Time Zone 'UTC'

也-不要在where子句中的列上使用函数，这会阻止SQL Server在该列上使用索引。

还有一件事-你的逻辑在1月1日不起作用，因为一年中的一天是1,1 - 1等于0，所以没有匹配。

您还需要小心定义开始日期。如果您尝试使用UTC，您可能会在错误的日期运行报告，这取决于它何时运行以及执行它的实例的偏移量。

Answer 2

我们已经将性能问题缩小到这个表达式:

DATEPART(dy,timestamp at time zone 'Pacific Standard Time') = 
datepart(dy,SYSDATETIMEOFFSET() at time zone 'Pacific Standard Time')-1

问题还告诉我们数据是用UTC存储的，并且我们知道(通过注释)服务器运行在NY。

---

首先，这是一个糟糕的做法。您的服务器应该使用与数据相同的时区运行。很多人会告诉你，这意味着一切都要保持UTC，但我不这么极端。如果您的业务完全在加利福尼亚，并且您的数据存储在加利福尼亚时间的datetime字段中，那么使用相同的时区运行服务器可能是合适的。但是，如果您的数据以UTC存储，那么您的数据库服务器也应该运行UTC时间，无论服务器位于何处。修复它(我说"修复"，因为你真正拥有的是破碎的))将完全解决问题中的问题。

---

但是预计修复将不在你的手中，我们仍然可以帮助一些事情。

当你有一个WHERE条件表达式，其中一边涉及表中的一列而另一边不涉及时，你总是会得到MUCHALL对不引用任何列的表达式一侧进行的调整。还差得远……我们讨论的是多个数量级的差异。

这意味着你想要做任何需要做的事情，以使有问题的表达式看起来像这样:

timestamp = SOME_COMPLICATED_AND_LONG_EXPRESSION_HERE( SYSDATETIMEOFFSET() )

您从中获得的最大好处是能够使用来自timestamp列的索引。如果您必须对表达式的timestamp侧进行调整，则不再使用与索引相同的值。这就触及了数据库性能的核心，并且它将使日夜不同。

更糟的是，如果不能这样做，则必须为表中的每一行计算对timestamp列所做的任何操作…甚至是您不需要的行，因为服务器在完成转换之前无法知道给定值是否将被使用。

---

在这种情况下，您可能无法避免表达式的DATEPART(dy...)部分。但是您可以对表达式的右侧进行从NY时间到UTC时间的调整。这将让您跳过左侧的at time zone部分，这将是一个巨大的帮助。这里值得注意的是，由于政府的长期干预，这些时区调整查找比您可能认为的要复杂得多。一个总的时区调整可以大大改善查询，而不需要对每一行进行调整。.