使用 AT TIME ZONE 获取指定时区的当前时间 [英] Using AT TIME ZONE to get current time in specified time zone

问题描述

我正在尝试使用新的 SQL Server 2016 和 Azure SQL 中的 AT TIME ZONE 语法.我只是想将伦敦的当前时间作为 datetime，针对夏令时进行调整.在运行以下所有命令时，伦敦时间是凌晨 3.27.

I am trying to use the new AT TIME ZONE syntax in SQL Server 2016 and Azure SQL. I'm just trying to get the current time in London as a datetime, adjusted for daylight saving. At the time of running all of the commands below, the time in London was 3.27am.

第一步是得到一个datetimeoffset，我可以成功做到如下:

The first step is to get a datetimeoffset, which I can successfully do as follows:

DECLARE @dto datetimeoffset
SET @dto = (SELECT GETUTCDATE() AT TIME ZONE 'GMT Standard Time')
SELECT @dto

这将返回一个我期望的值:

This returns a value as I would expect:

2016-04-04 02:27:54.0200000 +01:00

接下来，我想将其转换为 datetime，这是我的应用程序所期望的.我尝试了三种不同的方法，但都没有给我想要的结果:

Next, I want to convert that to a datetime, which is what my applications expect. I've tried three different approaches, none of which give me the result I'm looking for:

SELECT SWITCHOFFSET(@dto,'+00:00')
-- Returns 2016-04-04 01:27:54.0200000 +00:00

SELECT CONVERT(datetime, @dto)
-- Returns 2016-04-04 02:27:54.020

SELECT CONVERT(datetime2, @dto)
-- Returns 2016-04-04 02:27:54.0200000

我觉得我遗漏了一些明显的东西 - 有没有一种简单的方法来获取 datetimeoffset 并仅返回该偏移量处的日期/时间部分?

I feel like I'm missing something obvious - is there an easy way to take a datetimeoffset and return just the date/time part at that offset?

推荐答案

你的代码的第一行包含错误:

The first line of your code contains the fault:

SELECT GETUTCDATE() AT TIME ZONE 'GMT Standard Time'

GETUTCDATE() 返回一个 datetime，它没有时区偏移信息.因此，如 MSDN 文档 中所述:

GETUTCDATE() returns a datetime, which has no time zone offset information. Thus as described in the MSDN documentation:

如果 inputdate 没有提供偏移量信息，则该函数应用时区的偏移量，假设在目标时区中提供了 inputdate 值.

If inputdate is provided without offset information, the function applies the offset of the time zone assuming that inputdate value is provided in the target time zone.

因此，即使您检索了 UTC 时间，您也错误地断言该值是伦敦时间(该日期的夏令时为 UTC+1).

So, even though you retrieved the UTC time, you erroneously asserted that the value was in London time (which is UTC+1 for daylight saving time at this date).

处理这个问题的最简单方法是获取 UTC 时间作为 datetimeoffset 开始.

The easiest way to handle this is to just fetch the UTC time as a datetimeoffset to begin with.

SELECT SYSDATETIMEOFFSET() AT TIME ZONE 'GMT Standard Time'

这会调用 AT TIME ZONE 的 conversion 功能，在文档中说明:

This invokes the conversion functionality of AT TIME ZONE, which in the docs states:

如果 inputdate 作为 datetimeoffset 值提供，则 AT TIME ZONE 子句使用时区转换规则将其转换为目标时区.

If inputdate is provided as a datetimeoffset value, then AT TIME ZONE clause converts it into the target time zone using time zone conversion rules.

考虑到如果您的数据实际上来自某个地方的 datetime 字段，您可能需要使用两个部分功能，如下所示:

Consider that if your data actually comes from a datetime field somewhere, you might need to use both parts of the functionality, like this:

SELECT mydatetimefield AT TIME ZONE 'UTC' AT TIME ZONE 'GMT Standard Time'

对 AT TIME ZONE 的第一次调用断言该值是 UTC，将 datetimeoffset 提供给第二次调用，从而将其转换为伦敦时间.

The first call to AT TIME ZONE asserts the value is in UTC, giving a datetimeoffset to the second call, which converts it to London time.

其中任何一个的输出都是 datetimeoffset，您可以完全按照您在你原来的问题.(不要为此使用 switchoffset.)

The output of any of these is a datetimeoffset, which you can cast or convert to a datetime or datetime2 exactly as you showed in your original question. (Don't use switchoffset for this.)

此外，伦敦的 Windows 时区标识符始终为 GMT 标准时间".它包括格林威治标准时间和英国夏令时间，并在它们之间进行适当的转换.不要尝试将其更改为 "GMT Daylight Time" - 该标识符不存在.这也在 时区标签维基的 Windows 时区部分中有所介绍.

Also, the Windows time zone identifier for London is always "GMT Standard Time". It is inclusive of both Greenwich Mean Time and British Summer Time, with the appropriate transitions between them. Do not try change it to "GMT Daylight Time" - that identifier doesn't exist. This is also covered in the timezone tag wiki, in the section on Windows time zones.

这篇关于使用 AT TIME ZONE 获取指定时区的当前时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！