SQL 在select语句中将日期时间列从UTC转换为本地时间
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8038744/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Convert Datetime column from UTC to local time in select statement
提问by Nugs
I'm doing a few SQL select queries and would like to convert my UTC datetime column into local time to be displayed as local time in my query results. Note, I am NOT looking to do this conversion via code but rather when I am doing manual and random SQL queries against my databases.
我正在执行一些 SQL 选择查询，并希望将我的 UTC 日期时间列转换为本地时间以在查询结果中显示为本地时间。请注意，我不希望通过代码进行这种转换，而是在对我的数据库进行手动和随机 SQL 查询时。
回答by Michael Goldshteyn
You can do this as follows on SQL Server 2008 or greater:
您可以在 SQL Server 2008 或更高版本上执行以下操作：
SELECT CONVERT(datetime,
SWITCHOFFSET(CONVERT(datetimeoffset,
MyTable.UtcColumn),
DATENAME(TzOffset, SYSDATETIMEOFFSET())))
AS ColumnInLocalTime
FROM MyTable
You can also do the less verbose:
您还可以执行不那么冗长的操作：
SELECT DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), MyTable.UtcColumn)
AS ColumnInLocalTime
FROM MyTable
Whatever you do, do notuse -to subtract dates, because the operation is not atomic, and you will on occasion get indeterminate results due to race conditions between the system datetime and the local datetime being checked at different times (i.e., non-atomically).
无论您做什么，都不要使用-减去日期，因为该操作不是原子的，并且由于系统日期时间和在不同时间（即非原子地）检查的本地日期时间之间的竞争条件，您有时会得到不确定的结果.
Please note that this answer does not take DST into account. If you want to include a DST adjustment, please also see the following SO question:
请注意，此答案未考虑 DST。如果您想包括 DST 调整，还请参阅以下 SO 问题：
How to create Daylight Savings time Start and End function in SQL Server
回答by Aiden Kaskela
I didn't find any of these example helpful in getting a datetime stored as UTC to a datetime in a specified timezone (NOT the timezone of the server because Azure SQL databases run as UTC). This is how I handled it. It's not elegant but it's simple and gives you the right answer without maintaining other tables:
我没有发现这些示例中的任何一个都有助于将存储为 UTC 的日期时间转换为指定时区（不是服务器的时区，因为 Azure SQL 数据库以 UTC 运行）中的日期时间。我就是这样处理的。它并不优雅，但很简单，无需维护其他表即可为您提供正确答案：
select CONVERT(datetime, SWITCHOFFSET(dateTimeField, DATEPART(TZOFFSET,
dateTimeField AT TIME ZONE 'Eastern Standard Time')))
回答by Ron Smith
If you need a conversion other than your server's location, here is a function that allows you to pass a standard offset and accounts for US Daylight Savings Times:
如果您需要除服务器位置之外的转换，这里有一个函数，它允许您传递标准偏移量和美国夏令时的帐户：
-- =============================================
-- Author: Ron Smith
-- Create date: 2013-10-23
-- Description: Converts UTC to DST
-- based on passed Standard offset
-- =============================================
CREATE FUNCTION [dbo].[fn_UTC_to_DST]
(
@UTC datetime,
@StandardOffset int
)
RETURNS datetime
AS
BEGIN
declare
@DST datetime,
@SSM datetime, -- Second Sunday in March
@FSN datetime -- First Sunday in November
-- get DST Range
set @SSM = datename(year,@UTC) + '0314'
set @SSM = dateadd(hour,2,dateadd(day,datepart(dw,@SSM)*-1+1,@SSM))
set @FSN = datename(year,@UTC) + '1107'
set @FSN = dateadd(second,-1,dateadd(hour,2,dateadd(day,datepart(dw,@FSN)*-1+1,@FSN)))
-- add an hour to @StandardOffset if @UTC is in DST range
if @UTC between @SSM and @FSN
set @StandardOffset = @StandardOffset + 1
-- convert to DST
set @DST = dateadd(hour,@StandardOffset,@UTC)
-- return converted datetime
return @DST
END
GO
回答by Matt Frear
If your local date time is say Eastern Standard Timeand you want to convert from UTC to that, then in Azure SQL and SQL Server 2016 and above, you can do:
如果您说的是本地日期时间Eastern Standard Time并且您想从 UTC 转换为该时间，那么在 Azure SQL 和 SQL Server 2016 及更高版本中，您可以执行以下操作：
SELECT YourUtcColumn AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time' AS
LocalTime
FROM YourTable
The full list of timezone names can be found with:
可以通过以下方式找到时区名称的完整列表：
SELECT * FROM sys.time_zone_info
And yes, the timezones are badly named - even though it is Eastern Standard Time, daylight savings is taken into account.
是的，时区的命名很糟糕 - 即使是Eastern Standard Time，也考虑了夏令时。
回答by Pavel Samoylenko
Using new SQL Server 2016 opportunities:
使用新的 SQL Server 2016 机会：
CREATE FUNCTION ToLocalTime(@dtUtc datetime, @timezoneId nvarchar(256))
RETURNS datetime
AS BEGIN
return @dtUtc AT TIME ZONE 'UTC' AT TIME ZONE @timezoneId
/* -- second way, faster
return SWITCHOFFSET(@dtUtc , DATENAME(tz, @dtUtc AT TIME ZONE @timezoneId))
*/
/* -- third way
declare @dtLocal datetimeoffset
set @dtLocal = @dtUtc AT TIME ZONE @timezoneId
return dateadd(minute, DATEPART (TZoffset, @dtLocal), @dtUtc)
*/
END
GO
But clr procedure works in 5 times faster :'-(
但是 clr 程序的运行速度快了 5 倍:'-(
Pay attention that Offset for one TimeZone can change to winter or summer time. For example
请注意，一个 TimeZone 的 Offset 可以更改为冬季或夏季时间。例如
select cast('2017-02-08 09:00:00.000' as datetime) AT TIME ZONE 'Eastern Standard Time'
select cast('2017-08-08 09:00:00.000' as datetime) AT TIME ZONE 'Eastern Standard Time'
results:
结果：
2017-02-08 09:00:00.000 -05:00
2017-08-08 09:00:00.000 -04:00
You can't just add constant offset.
您不能只添加常量偏移量。
回答by vikjon0
There is no simple way to do this in a correct AND generic way.
没有简单的方法可以以正确且通用的方式执行此操作。
First of all it must be understood that the offset depends on the date in question, the Time Zone AND DST.
GetDate()-GetUTCDateonly gives you the offset today at the server's TZ, which is not relevant.
首先，必须了解偏移量取决于相关日期、时区和夏令时。
GetDate()-GetUTCDate仅在服务器的 TZ 处为您提供今天的偏移量，这无关紧要。
I have seen only two working solution and I have search a lot.
我只看到了两个有效的解决方案，而且我搜索了很多。
1) A custom SQL function with a a couple of tables of base data such as Time Zones and DST rules per TZ. Working but not very elegant. I can't post it since I don't own the code.
1) 一个自定义 SQL 函数，带有几个基本数据表，例如每个 TZ 的时区和 DST 规则。工作但不是很优雅。我不能发布它，因为我没有代码。
EDIT: Here is an example of this method https://gist.github.com/drumsta/16b79cee6bc195cd89c8
编辑：这是此方法的示例 https://gist.github.com/drumsta/16b79cee6bc195cd89c8
2) Add a .net assembly to the db, .Net can do this very easily. This is working very well but the downside is that you need to configure several parameters on server level and the config is easily broken e.g. if you restore the database. I use this method but I cant post it since I don't own the code.
2) 将 .net 程序集添加到数据库中，.Net 可以很容易地做到这一点。这工作得很好，但缺点是您需要在服务器级别配置多个参数，并且配置很容易被破坏，例如，如果您恢复数据库。我使用这种方法，但我不能发布它，因为我不拥有代码。
回答by JGates
If enabling CLR on your database is an option as well as using the sql server's timezone, it can be written in .Net quite easily.
如果在您的数据库上启用 CLR 是一个选项以及使用 sql server 的时区，那么它可以很容易地用 .Net 编写。
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlDateTime fn_GetLocalFromUTC(SqlDateTime UTC)
{
if (UTC.IsNull)
return UTC;
return new SqlDateTime(UTC.Value.ToLocalTime());
}
}
A UTC datetime value goes in and the local datetime value relative to the server comes out. Null values return null.
输入一个 UTC 日期时间值，然后输出相对于服务器的本地日期时间值。空值返回空值。
回答by Mike
None of these worked for me but this below worked 100%. Hope this can help others trying to convert it like I was.
这些都不适合我，但下面的这个工作 100%。希望这可以帮助其他人像我一样尝试转换它。
CREATE FUNCTION [dbo].[fn_UTC_to_EST]
(
@UTC datetime,
@StandardOffset int
)
RETURNS datetime
AS
BEGIN
declare
@DST datetime,
@SSM datetime, -- Second Sunday in March
@FSN datetime -- First Sunday in November
-- get DST Range
set @SSM = DATEADD(dd,7 + (6-(DATEDIFF(dd,0,DATEADD(mm,(YEAR(GETDATE())-1900) * 12 + 2,0))%7)),DATEADD(mm,(YEAR(GETDATE())-1900) * 12 + 2,0))+'02:00:00'
set @FSN = DATEADD(dd, (6-(DATEDIFF(dd,0,DATEADD(mm,(YEAR(GETDATE())-1900) * 12 + 10,0))%7)),DATEADD(mm,(YEAR(GETDATE())-1900) * 12 + 10,0)) +'02:00:00'
-- add an hour to @StandardOffset if @UTC is in DST range
if @UTC between @SSM and @FSN
set @StandardOffset = @StandardOffset + 1
-- convert to DST
set @DST = dateadd(hour,@StandardOffset,@UTC)
-- return converted datetime
return @DST
END
回答by Patrick Slesicki
Here's a version that accounts for daylight savings, UTC offset, and is not locked into a particular year.
这是一个考虑了夏令时、UTC 偏移量且未锁定到特定年份的版本。
---------------------------------------------------------------------------------------------------
--Name: udfToLocalTime.sql
--Purpose: To convert UTC to local US time accounting for DST
--Author: Patrick Slesicki
--Date: 3/25/2014
--Notes: Works on SQL Server 2008R2 and later, maybe SQL Server 2008 as well.
-- Good only for US States observing the Energy Policy Act of 2005.
-- Function doesn't apply for years prior to 2007.
-- Function assumes that the 1st day of the week is Sunday.
--Tests:
-- SELECT dbo.udfToLocalTime('2014-03-09 9:00', DEFAULT)
-- SELECT dbo.udfToLocalTime('2014-03-09 10:00', DEFAULT)
-- SELECT dbo.udfToLocalTime('2014-11-02 8:00', DEFAULT)
-- SELECT dbo.udfToLocalTime('2014-11-02 9:00', DEFAULT)
---------------------------------------------------------------------------------------------------
ALTER FUNCTION udfToLocalTime
(
@UtcDateTime AS DATETIME
,@UtcOffset AS INT = -8 --PST
)
RETURNS DATETIME
AS
BEGIN
DECLARE
@PstDateTime AS DATETIME
,@Year AS CHAR(4)
,@DstStart AS DATETIME
,@DstEnd AS DATETIME
,@Mar1 AS DATETIME
,@Nov1 AS DATETIME
,@MarTime AS TIME
,@NovTime AS TIME
,@Mar1Day AS INT
,@Nov1Day AS INT
,@MarDiff AS INT
,@NovDiff AS INT
SELECT
@Year = YEAR(@UtcDateTime)
,@MarTime = CONVERT(TIME, DATEADD(HOUR, -@UtcOffset, '1900-01-01 02:00'))
,@NovTime = CONVERT(TIME, DATEADD(HOUR, -@UtcOffset - 1, '1900-01-01 02:00'))
,@Mar1 = CONVERT(CHAR(16), @Year + '-03-01 ' + CONVERT(CHAR(5), @MarTime), 126)
,@Nov1 = CONVERT(CHAR(16), @Year + '-11-01 ' + CONVERT(CHAR(5), @NovTime), 126)
,@Mar1Day = DATEPART(WEEKDAY, @Mar1)
,@Nov1Day = DATEPART(WEEKDAY, @Nov1)
--Get number of days between Mar 1 and DST start date
IF @Mar1Day = 1 SET @MarDiff = 7
ELSE SET @MarDiff = 15 - @Mar1Day
--Get number of days between Nov 1 and DST end date
IF @Nov1Day = 1 SET @NovDiff = 0
ELSE SET @NovDiff = 8 - @Nov1Day
--Get DST start and end dates
SELECT
@DstStart = DATEADD(DAY, @MarDiff, @Mar1)
,@DstEnd = DATEADD(DAY, @NovDiff, @Nov1)
--Change UTC offset if @UtcDateTime is in DST Range
IF @UtcDateTime >= @DstStart AND @UtcDateTime < @DstEnd SET @UtcOffset = @UtcOffset + 1
--Get Conversion
SET @PstDateTime = DATEADD(HOUR, @UtcOffset, @UtcDateTime)
RETURN @PstDateTime
END
GO
回答by JBrooks
I found the one off function way to be too slow when there is a lot of data. So I did it through joining to a table function that would allow for a calculation of the hour diff. It is basically datetime segments with the hour offset. A year would be 4 rows. So the table function
我发现当有大量数据时，一次性函数方式太慢了。所以我通过加入一个允许计算小时差异的表函数来做到这一点。它基本上是带有小时偏移量的日期时间段。一年将是 4 行。所以表函数
dbo.fn_getTimeZoneOffsets('3/1/2007 7:00am', '11/5/2007 9:00am', 'EPT')
would return this table:
将返回此表：
startTime endTime offset isHr2
3/1/07 7:00 3/11/07 6:59 -5 0
3/11/07 7:00 11/4/07 6:59 -4 0
11/4/07 7:00 11/4/07 7:59 -5 1
11/4/07 8:00 11/5/07 9:00 -5 0
It does account for daylight savings. A sample of how it is uses is below and the full blog post is here.
它确实考虑了夏令时。下面是它如何使用的示例，完整的博客文章在这里。
select mt.startTime as startUTC,
dateadd(hh, tzStart.offset, mt.startTime) as startLocal,
tzStart.isHr2
from MyTable mt
inner join dbo.fn_getTimeZoneOffsets(@startViewUTC, @endViewUTC, @timeZone) tzStart
on mt.startTime between tzStart.startTime and tzStart.endTime
