使用SQL查询将日期时间值从一个时区转换为UTC时区

Question

使用SQL查询将日期时间值从一个时区转换为UTC时区

sql-serverdatetime

26

我有一个日期时间值。

该日期时间值可能位于任何时区，例如“东部标准时间”或“印度标准时间”。

我想在SQL中将该日期时间值转换为UTC时区。

这里的时区值将作为给定参数。

我也可以使用C#代码实现此目标。但是我需要SQL查询。

有人能告诉我如何转换吗？

- user1934329

这些字段的数据类型是什么？ - qxg

3

可能是SQL Server中的时区转换日期时间的重复问题。 - Eduardo Yáñez Parareda

使用datetimeoffset不需要进行任何转换，这样不是更好吗？ - Panagiotis Kanavos

@EduardoYáñezParareda，“duplicate”根本不相关。这是一个关于UNIX时间戳的问题。该问题询问如何使用时区/偏移量作为参数将日期时间转换为UTC。 - Panagiotis Kanavos

你面临的一个问题是如何确定要转换的日期时间值是否包含夏令时。 - Steve Ford

5个回答

8

如果你正在使用 SQL Server 2016，则可以使用新的 AT TIME ZONE 子句：

SELECT SalesOrderID, OrderDate,   
    OrderDate AT TIME ZONE 'Eastern Standard Time' AS OrderDate_TimeZoneEST,  
    OrderDate AT TIME ZONE 'Eastern Standard Time'   
    AT TIME ZONE 'UTC' AS OrderDate_TimeZoneUTC  
FROM Sales.SalesOrderHeader;

- Steve Ford

5

要将一个时区转换为另一个时区，请使用 AT TIME ZONE 命令，

例如，如果你有一个名为 Book 的表，其中存储了以 UTC 为单位的 purchasedTime，并且你想知道在 EST 时区的时间。

Select bookName , CONVERT(datetime,purchasedTime) AT TIME ZONE 'UTC' AT TIME ZONE 'US Eastern Standard Time' as purchaseTimeInEST from Book;

这导致所有购买的书籍都是在东部标准时间购买的。

- Mithun abisheg

1

如果您使用的是早于SQL Server 2012版本的数据库，请使用以下查询语句，它对我很有效。

DECLARE @localdate DATETIME = '2020-01-10 05:30';
Declare @DateDiff bigint =Datediff(minute, Getdate(),Getutcdate() ) --GetDate -current datetime of local
--Declare @DateDiff bigint =-330  --you ca declare timezone different in minutes also
SELECT @localdate AS LocalDate, Dateadd(minute, @DateDiff, @localdate) AS UTCConvertedDate;

- Pandi_Snkl

0

使用时区名称转换时区，可以执行以下操作：

找到服务器时区和您的时区之间的分钟数。

-- Change to your time zone name
declare @TimezoneName varchar(max) = 'New Zealand Standard Time'
declare @timezoneOffset bigint = Datediff( MINUTE, getdate() at time zone @TimezoneName, getdate() )

使用SWITCHOFFSET可以将日期时间转换为您所在的时区。

例如，当getdate()为'2020-04-16 04:47:25.640'时，您将得到'2020-04-16 16:47:25.640 +12:00'（NZT时区为+12）。

select SWITCHOFFSET(getdate(), @timezoneOffset)

此方法可在SQL Server 2016及以上版本中使用。

- Jono

1

你还要执行双重偏移量转换，只不过这一次你通过一个非标准时区名称获取偏移量，然后使用偏移量转换。由于SQL Server仅存储偏移量而不是时区名称（标准或非标准），因此无法避免这种情况。 - Panagiotis Kanavos

1

顺便提一下，这个查询不会起作用，因为 GETDATE() 会返回本地时间。这将为具有不同设置的服务器返回不同的结果。在任何默认时区为 UTC 的云服务器上，它肯定会失败。更加理由是通过使用 DateTimeOffset 来避免转换。 - Panagiotis Kanavos

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Panagiotis Kanavos · Accepted Answer

时区和时区偏移是两个不同的概念。如果使用夏令时，一个时区可以有不同的偏移量。 SQL Server在最新版本2016中添加了时区支持。

你的问题有两部分 - 如何将datetime值转换为带有偏移/时区的值，然后如何将该值转换为UTC时间。

在SQL Server 2014之前的版本中，您需要事先确定本地时区的正确偏移量，例如使用C#代码。一旦您拥有它，就可以使用TODATETIMEOFFSET将datetime转换为具有特定偏移量的`datetimeoffset：

select TODATETIMEOFFSET(GETDATE(),'02:00')

或者

select TODATETIMEOFFSET(GETDATE(),120)

这将返回一个带有原始时间和指定偏移量的datetimeoffset值。

切换到另一个偏移量（例如UTC）可通过SWITCHOFFSET函数执行。

select SWITCHOFFSET(@someDateTimeOffset,0)

您可以将它们组合起来使用

select SWITCHOFFSET(TODATETIMEOFFSET(GETDATE(),120),0)

偏移量可以作为参数传递。假设您的字段名为SomeTime，您可以编写：

select SWITCHOFFSET(TODATETIMEOFFSET(SomeTime,@offsetInMinutes),0)

在SQL Server 2016中，您可以使用时区名称。但是您仍然需要进行双重转换，首先转换为本地时区，然后再转换为UTC：

SELECT (getdate() at time zone 'Central Europe Standard Time') AT TIME ZONE 'UTC'

第一个AT TIMEZONE返回一个带有+2:00偏移量的datetimeoffset，第二个将其转换为UTC。

注意

如果您使用datetimeoffset类型而不是datetime，则可能可以避免所有转换。 SQL Server允许对具有不同偏移量的值进行比较、过滤、计算等操作，因此您无需进行任何查询转换。在客户端方面，.NET有等效的DateTimeOffset类型，因此您无需在客户端代码中进行任何转换。