时间偏移（Paris-Kuwait）时区差异不正确SQL Server Download


我使用查询根据科威特时区过滤从星期五午夜到星期六午夜的记录。

首先，当我检查服务器时区时，我可以看到：

select CURRENT_TIMEZONE();

(UTC+01:00) Brussels, Copenhagen, Madrid, Paris

所以我认为我需要过滤的日期列基于此时区，因为记录是在服务器上创建和存储的，对吧？

然后我有我的查询：

SELECT
    ID,
    DATETIME,
    DATETIME AT TIME ZONE 'Arab Standard Time' AS kuwait_time 
FROM
    admin_all.ACCOUNT_TRAN_ALL 
WHERE
    DATETIME BETWEEN (DATEADD(wk, DATEDIFF(wk, 6, GETDATE()), 5) AT TIME ZONE 'Arab Standard Time') 
                 AND (DATEADD(wk, DATEDIFF(wk, 6, GETDATE()), 6) AT TIME ZONE 'Arab Standard Time')

哪个返回此输出：

+---------+-------------------------+--------------------------------+
| ID      | DATETIME                | kuwait_time                    |
+---------+-------------------------+--------------------------------+
| 1050554 | 2019-12-27 21:05:28.073 | 2019-12-27 21:05:28.073 +03:00 |
| 1050555 | 2019-12-27 21:05:42.586 | 2019-12-27 21:05:42.587 +03:00 |
| 1050556 | 2019-12-27 21:06:58.920 | 2019-12-27 21:06:58.920 +03:00 |
| 1050557 | 2019-12-27 21:07:12.906 | 2019-12-27 21:07:12.907 +03:00 |
| 1050558 | 2019-12-27 21:16:56.436 | 2019-12-27 21:16:56.437 +03:00 |
| 1050559 | 2019-12-27 21:17:10.533 | 2019-12-27 21:17:10.533 +03:00 |
| 1050560 | 2019-12-27 21:17:37.913 | 2019-12-27 21:17:37.913 +03:00 |
| 1050561 | 2019-12-27 21:17:37.986 | 2019-12-27 21:17:37.987 +03:00 |
+---------+-------------------------+--------------------------------+

现在是我的问题。由于我希望这从上周五的午夜开始，并在上周六的午夜结束，并且由于我的本地DATETIME字段是UTC + 1，因此我应该在22:05开始我的第一条记录，以使其在午夜从科威特时间开始。比我早2小时（我是UTC + 1，科威特是UTC + 3）。

为什么我要在21:05过滤第一条记录？

更新：在@Matt的友善支持之后，我想到了此查询，该查询带有他的一些提示，但在where参数中也包含了最后一个星期五和星期六的搜索，在答案中。

现在查询非常慢，必须有一种加快查询速度的方法。它给出了正确的结果：

SELECT id, 
       datetime at time zone 'Romance Standard Time'                                   AS local_time,
       datetime at time zone 'Romance Standard Time' at time zone 'Arab Standard Time' AS kuwait_date
FROM   admin_all.account_tran_all 
WHERE  datetime at time zone 'Romance Standard Time' at time zone 'Arab Standard Time' >= 
       ( 
              SELECT CONVERT(datetime, 
                     CASE 
                            WHEN datename(weekday,cast(CURRENT_TIMESTAMP AS date)) = 'Sunday' THEN dateadd(day,-2,cast(CURRENT_TIMESTAMP AS date))
                            WHEN datename(weekday,cast(CURRENT_TIMESTAMP AS date)) = 'Monday' THEN dateadd(day,-3,cast(CURRENT_TIMESTAMP AS date))
                            WHEN datename(weekday,cast(CURRENT_TIMESTAMP AS date)) = 'Tuesday' THEN dateadd(day,-4,cast(CURRENT_TIMESTAMP AS date))
                            WHEN datename(weekday,cast(CURRENT_TIMESTAMP AS date)) = 'Wednesday' THEN dateadd(day,-5,cast(CURRENT_TIMESTAMP AS date))
                            WHEN datename(weekday,cast(CURRENT_TIMESTAMP AS date)) = 'Thursday' THEN dateadd(day,-6,cast(CURRENT_TIMESTAMP AS date))
                            WHEN datename(weekday,cast(CURRENT_TIMESTAMP AS date)) = 'Friday' THEN dateadd(day,-7,cast(CURRENT_TIMESTAMP AS date))
                            WHEN datename(weekday,cast(CURRENT_TIMESTAMP AS date)) = 'Saturday' THEN dateadd(day,-8,cast(CURRENT_TIMESTAMP AS date))
                     END ) at time zone 'Arab Standard Time') 
AND    datetime at time zone 'Romance Standard Time' at time zone 'Arab Standard Time' < 
       ( 
              SELECT CONVERT(datetime, 
                     CASE 
                            WHEN datename(weekday,cast(CURRENT_TIMESTAMP AS date)) = 'Sunday' THEN dateadd(day,-1,cast(CURRENT_TIMESTAMP AS date))
                            WHEN datename(weekday,cast(CURRENT_TIMESTAMP AS date)) = 'Monday' THEN dateadd(day,-2,cast(CURRENT_TIMESTAMP AS date))
                            WHEN datename(weekday,cast(CURRENT_TIMESTAMP AS date)) = 'Tuesday' THEN dateadd(day,-3,cast(CURRENT_TIMESTAMP AS date))
                            WHEN datename(weekday,cast(CURRENT_TIMESTAMP AS date)) = 'Wednesday' THEN dateadd(day,-4,cast(CURRENT_TIMESTAMP AS date))
                            WHEN datename(weekday,cast(CURRENT_TIMESTAMP AS date)) = 'Thursday' THEN dateadd(day,-5,cast(CURRENT_TIMESTAMP AS date))
                            WHEN datename(weekday,cast(CURRENT_TIMESTAMP AS date)) = 'Friday' THEN dateadd(day,-6,cast(CURRENT_TIMESTAMP AS date))
                            WHEN datename(weekday,cast(CURRENT_TIMESTAMP AS date)) = 'Saturday' THEN dateadd(day,-7,cast(CURRENT_TIMESTAMP AS date))
                     END) at time zone 'Arab Standard Time' )

结果：

| ID          | Original_DATETIME       | Local_time                     | kuwait_date                    |
|-------------|-------------------------|--------------------------------|--------------------------------|
| 1046053     | 2019-12-26 22:00:01.836 | 2019-12-26 22:00:01.837 +01:00 | 2019-12-27 00:00:01.837 +03:00 |
| 1046054     | 2019-12-26 22:00:01.940 | 2019-12-26 22:00:01.940 +01:00 | 2019-12-27 00:00:01.940 +03:00 |
| 1046055     | 2019-12-26 22:00:17.040 | 2019-12-26 22:00:17.040 +01:00 | 2019-12-27 00:00:17.040 +03:00 |
| 1046056     | 2019-12-26 22:00:19.046 | 2019-12-26 22:00:19.047 +01:00 | 2019-12-27 00:00:19.047 +03:00 |
| 1046057     | 2019-12-26 22:00:19.156 | 2019-12-26 22:00:19.157 +01:00 | 2019-12-27 00:00:19.157 +03:00 |
| 1046058     | 2019-12-26 22:00:44.646 | 2019-12-26 22:00:44.647 +01:00 | 2019-12-27 00:00:44.647 +03:00 |
| 1046059     | 2019-12-26 22:00:44.713 | 2019-12-26 22:00:44.713 +01:00 | 2019-12-27 00:00:44.713 +03:00 |
| 1046060     | 2019-12-26 22:00:47.483 | 2019-12-26 22:00:47.483 +01:00 | 2019-12-27 00:00:47.483 +03:00 |

1 个答案:

答案 0 :(得分：2)

  

所以我假设我需要过滤的Date字段基于该时区，因为记录是在此服务器上创建和存储的，对吧？

对不起，但不是-那是不对的。数据库中存储的字段与服务器的时区之间没有隐式绑定。 GETDATE()函数使用服务器的时区 来确定本地时间，但随后将其丢弃。

对于DATETIME和DATETIME2字段，您必须设计数据库以使自己知道时区引用是什么。例如，通常以UTC为参考。如果您以巴黎时间为参考，那么在进行转化时就必须知道这一点。

AT TIME ZONE的工作方式如下：

  • 使用DATETIME和DATETIME2类型，AT TIME ZONE语句仅声明所需的时区。结果是DATETIMEOFFSET类型，其本地日期和时间与原始值相同，但应用了该时区的正确偏移量。

  • 对于DATETIMEOFFSET类型，AT TIME ZONE语句从给定时区的值进行转换。返回具有世界标准时间相同点的DATETIMEOFFSET，但是日期，时间和偏移会更改以反映新的时区。

因此，如果您以DATETIME或DATETIME2字段开头，则需要两条 AT TIME ZONE语句才能从一个时区转换为另一个时区。 

SELECT
    DATETIME AT TIME ZONE 'Romance Standard Time' as paris_time,
    DATETIME AT TIME ZONE 'Romance Standard Time' AT TIME ZONE 'Arab Standard Time' AS kuwait_time
    ...

更简单的解决方案是使用DATETIMEOFFSET字段开始。这样，您就不必对时区参考有任何隐含的知识。

我还建议为起点/终点声明局部变量。这将使您的代码更具可读性。

DECLARE @NowInKuwait DATETIMEOFFSET = SYSDATETIMEOFFSET() AT TIME ZONE 'Arab Standard Time';
DECLARE @Start DATETIMEOFFSET = DATEADD(wk, 5, @NowInKuwait) AT TIME ZONE 'Arab Standard Time';
DECLARE @End DATETIMEOFFSET = DATEADD(wk, 6, @NowInKuwait) AT TIME ZONE 'Arab Standard Time';

SELECT ...
FROM   ...
WHERE DATETIME >= @Start AND DATETIME < @End

由于您问题中的DATEADD和DATEDIFF陈述式没有正确的参数，我在猜测您想要的开始和结束时间。

还请注意，在AT TIME ZONE和@Start的声明中，我再次调用@End。在这种情况下，偏移量在“现在”和生成的日期之间发生了变化。如果仅将它用于科威特，则可以将其忽略，因为科威特已固定为UTC + 3。但是，许多时区都使用夏令时或更改了其标准时区，因此不能总是认为时区是一致的。



Similar searches