在选择语句中将Datetime列从UTC转换为本地时间

小开

最佳答案

你可以在SQL Server 2008或更高版本上这样做:

SELECT CONVERT(datetime,
SWITCHOFFSET(CONVERT(datetimeoffset,
MyTable.UtcColumn),
DATENAME(TzOffset, SYSDATETIMEOFFSET())))
AS ColumnInLocalTime
FROM MyTable

你也可以用更简洁的方法:

SELECT DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), MyTable.UtcColumn)
AS ColumnInLocalTime
FROM MyTable

无论你做什么，不使用-来减去日期，因为这个操作不是原子的，而且由于在不同时间检查的系统datetime和本地datetime之间的竞争条件(即非原子的)，你有时会得到不确定的结果。

请注意，这个答案没有考虑夏令时。如果你想包含夏令时调整，也请参阅以下SO问题:

如何在SQL Server中创建夏令时开始和结束函数 . sh 如何在SQL Server中创建夏令时开始和结束函数

小开

如果你需要一个转换而不是你的服务器位置，这里有一个函数，允许你传递一个标准偏移量，并考虑美国夏令时:

-- =============================================
-- 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

小开

下面的版本考虑了夏令时、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

小开

我发现当有大量数据时，一次性函数的方法太慢了。因此，我通过连接到一个允许计算小时差的表函数来实现它，它基本上是带有小时偏移量的datetime分段。一年是4行。这个表格函数

dbo.fn_getTimeZoneOffsets('3/1/2007 7:00am', '11/5/2007 9:00am', 'EPT')

将返回这个表:

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

它确实考虑了夏时制。下面是如何使用它的示例，完整的博客文章是在这里。

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

小开

下面是一个将夏令时考虑在内的更简单的例子

CREATE FUNCTION [dbo].[UtcToLocal]
(
@p_utcDatetime DATETIME
)
RETURNS DATETIME
AS
BEGIN
RETURN DATEADD(MINUTE, DATEDIFF(MINUTE, GETUTCDATE(), @p_utcDatetime), GETDATE())
END

小开

作为一个警告-如果你要使用以下(注意毫秒而不是分钟):

    SELECT DATEADD(ms, DATEDIFF(ms, GETUTCDATE(), GETDATE()), MyTable.UtcColumn)
AS ColumnInLocalTime
FROM MyTable

请记住，DATEDIFF部分并不总是返回相同的数字。所以不要用它来将DateTimes精确到毫秒。

小开

如果在数据库上启用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());
}
}

输入一个UTC日期时间值，然后输出相对于服务器的本地日期时间值。Null值返回Null。

小开

罗恩的回答有一个错误。它使用当地时间凌晨2:00，其中需要UTC等效值。我没有足够的声望分数来评论Ron的回答，所以更正版本如下:

-- =============================================
-- 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 - @StandardOffset,dateadd(day,datepart(dw,@SSM)*-1+1,@SSM))
set @FSN = datename(year,@UTC) + '1107'
set @FSN = dateadd(second,-1,dateadd(hour,2 - (@StandardOffset + 1),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

小开

没有一种简单的方法能以正确而通用的方式做到这一点。

首先，必须理解偏移量取决于所讨论的日期、时区和夏令时。 GetDate()-GetUTCDate只给你今天在服务器TZ的偏移量，这是不相关的

我只见过两种有效的解决方案，我已经搜索了很多。

1)一个自定义SQL函数，包含几个基本数据表，如每个TZ的时区和夏令时规则。工作，但不是很优雅。我不能发布它，因为我不拥有代码

EDIT:下面是该方法的示例 https://gist.github.com/drumsta/16b79cee6bc195cd89c8 在db中添加一个。net程序集，.net可以很容易地做到这一点。这工作得很好，但缺点是你需要在服务器级配置几个参数，配置很容易被破坏，例如，如果你恢复数据库。我使用这个方法，但我不能张贴它，因为我没有自己的代码。

小开

我发现这个函数比使用单独的表或循环的其他解决方案更快。这只是一个基本的case语句。考虑到4月到10月之间的所有月份都有-4小时的偏移(东部时间)，我们只需要为边缘日添加更多的案例线。否则，偏移量为-5小时。

这是特定于从UTC到东部时间的转换，但可以根据需要添加其他时区功能。

USE [YourDatabaseName]
GO


/****** Object:  UserDefinedFunction [dbo].[ConvertUTCtoEastern]    Script Date: 11/2/2016 5:21:52 PM ******/
SET ANSI_NULLS ON
GO


SET QUOTED_IDENTIFIER ON
GO




CREATE FUNCTION [dbo].[ConvertUTCtoEastern]
(
@dtStartDate DATETIME
)
RETURNS DATETIME
AS
BEGIN
DECLARE @Working DATETIME
DECLARE @Returned DATETIME


SET @Working = @dtStartDate
SET @Working =
case when month(@Working) between 4 and 10 then dateadd(HH,-4,@Working)
when @Working between '2017-03-12' and '2017-11-05' then dateadd(HH,-4,@Working)
when @Working between '2016-03-13' and '2016-11-06' then dateadd(HH,-4,@Working)
when @Working between '2015-03-08' and '2015-11-01' then dateadd(HH,-4,@Working)
when @Working between '2014-03-09' and '2014-11-02' then dateadd(HH,-4,@Working)
when @Working between '2013-03-10' and '2013-11-03' then dateadd(HH,-4,@Working)
when @Working between '2012-03-11' and '2012-11-04' then dateadd(HH,-4,@Working)
else dateadd(HH,-5,@Working) end


SET @Returned = @Working


RETURN @Returned


END




GO

小开

使用新的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

但clr程序的工作速度快5倍:'-(

请注意，一个时区的偏移量可以更改为冬季时间或夏季时间。例如

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'

结果:

2017-02-08 09:00:00.000 -05:00
2017-08-08 09:00:00.000 -04:00

你不能只是添加常数偏移量。

小开

 declare @mydate2 datetime
set @mydate2=Getdate()
select @mydate2 as mydate,
dateadd(minute, datediff(minute,getdate(),@mydate2),getutcdate())

小开

我没有发现任何这些示例有助于将日期时间存储为UTC到指定时区(不是服务器的时区，因为Azure SQL数据库以UTC运行)中的日期时间。我是这样处理的。它并不优雅，但它很简单，无需维护其他表就能给出正确答案:

select CONVERT(datetime, SWITCHOFFSET(dateTimeField, DATEPART(TZOFFSET,
dateTimeField AT TIME ZONE 'Eastern Standard Time')))

小开

这些对我来说都没用，但下面这个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

小开

这样就可以获得与夏令时同步的服务器时间

declare @dt datetime
set @dt = getutcdate() -- GMT equivalent

sysdatetimeoffset会考虑夏令时

select [InputTime] = @dt
, [LocalTime2] = dateadd(mi, datediff(mi, sysdatetimeoffset(),getdate()), @dt)

小开

第一个功能:配置为意大利时区(+1，+2)，切换日期:3月和10月的最后一个星期天，返回当前时区与datetime的差值作为参数。

Returns:
current timezone < parameter timezone ==> +1
current timezone > parameter timezone ==> -1
else 0

代码是:

CREATE FUNCTION [dbo].[UF_ADJUST_OFFSET]
(
@dt_utc datetime2(7)
)
RETURNS INT
AS
BEGIN




declare @month int,
@year int,
@current_offset int,
@offset_since int,
@offset int,
@yearmonth varchar(8),
@changeoffsetdate datetime2(7)


declare @lastweek table(giorno datetime2(7))


select @current_offset = DATEDIFF(hh, GETUTCDATE(), GETDATE())


select @month = datepart(month, @dt_utc)


if @month < 3 or @month > 10 Begin Set @offset_since = 1 Goto JMP End


if @month > 3 and @month < 10 Begin Set @offset_since = 2 Goto JMP End


--If i'm here is march or october
select @year = datepart(yyyy, @dt_utc)


if @month = 3
Begin


Set @yearmonth = cast(@year as varchar) + '-03-'


Insert Into @lastweek Values(@yearmonth + '31 03:00:00.000000'),(@yearmonth + '30 03:00:00.000000'),(@yearmonth + '29 03:00:00.000000'),(@yearmonth + '28 03:00:00.000000'),
(@yearmonth + '27 03:00:00.000000'),(@yearmonth + '26 03:00:00.000000'),(@yearmonth + '25 03:00:00.000000')


--Last week of march
Select @changeoffsetdate = giorno From @lastweek Where  datepart(weekday, giorno) = 1


if @dt_utc < @changeoffsetdate
Begin
Set @offset_since = 1
End Else Begin
Set @offset_since = 2
End
End


if @month = 10
Begin


Set @yearmonth = cast(@year as varchar) + '-10-'


Insert Into @lastweek Values(@yearmonth + '31 03:00:00.000000'),(@yearmonth + '30 03:00:00.000000'),(@yearmonth + '29 03:00:00.000000'),(@yearmonth + '28 03:00:00.000000'),
(@yearmonth + '27 03:00:00.000000'),(@yearmonth + '26 03:00:00.000000'),(@yearmonth + '25 03:00:00.000000')


--Last week of october
Select @changeoffsetdate = giorno From @lastweek Where  datepart(weekday, giorno) = 1


if @dt_utc > @changeoffsetdate
Begin
Set @offset_since = 1
End Else Begin
Set @offset_since = 2
End
End


JMP:


if @current_offset < @offset_since Begin
Set @offset = 1
End Else if @current_offset > @offset_since Set @offset = -1 Else Set @offset = 0


Return @offset


END

然后是转换日期的函数

CREATE FUNCTION [dbo].[UF_CONVERT]
(
@dt_utc datetime2(7)
)
RETURNS datetime
AS
BEGIN


declare @offset int




Select @offset = dbo.UF_ADJUST_OFFSET(@dt_utc)


if @dt_utc >= '9999-12-31 22:59:59.9999999'
set @dt_utc = '9999-12-31 23:59:59.9999999'
Else
set @dt_utc = (SELECT DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), @dt_utc) )


if @offset <> 0
Set @dt_utc = dateadd(hh, @offset, @dt_utc)


RETURN @dt_utc


END

小开

UNIX时间戳只是一个特定日期和UNIX纪元之间的秒数，

SELECT DATEDIFF(第二,{d ' 1970-01-01 '},获取当前日期()) //返回SQL server中的UNIX时间戳

您可以创建一个函数，使用国家偏移量将本地日期时间转换为Unix UTC SQL server中的Unix时间戳函数

小开

从utc获取印度标准时间

CREATE FUNCTION dbo.getISTTime
(
@UTCDate datetime
)
RETURNS datetime
AS
BEGIN


RETURN dateadd(minute,330,@UTCDate)


END
GO

小开

这很简单。试试这个Azure SQL Server:

SELECT YourDateTimeColumn AT TIME ZONE 'Eastern Standard Time' FROM YourTable

对于本地SQL Server:

SELECT CONVERT(datetime2, SWITCHOFFSET(CONVERT(datetimeoffset, gETDATE()), DATENAME(TzOffset, gETDATE() AT TIME ZONE 'Eastern Standard Time'))) FROM YourTable

小开

这可以在没有函数的情况下完成。下面的代码将把UTC时间转换为考虑夏令时的山地时间。相应地调整所有的-6和-7数字到您的时区(即对于EST，您将分别调整为-4和-5)

--Adjust a UTC value, in the example the UTC field is identified as UTC.Field, to account for daylight savings time when converting out of UTC to Mountain time.
CASE
--When it's between March and November, it is summer time which is -6 from UTC
WHEN MONTH ( UTC.Field ) > 3 AND MONTH ( UTC.Field ) < 11
THEN DATEADD ( HOUR , -6 , UTC.Field )
--When its March and the day is greater than the 14, you know it's summer (-6)
WHEN MONTH ( UTC.Field ) = 3
AND DATEPART ( DAY , UTC.Field ) >= 14
THEN
--However, if UTC is before 9am on that Sunday, then it's before 2am Mountain which means it's still Winter daylight time.
CASE
WHEN DATEPART ( WEEKDAY , UTC.Field ) = 1
AND UTC.Field < '9:00'
--Before 2am mountain time so it's winter, -7 hours for Winter daylight time
THEN DATEADD ( HOUR , -7 , UTC.Field )
--Otherwise -6 because it'll be after 2am making it Summer daylight time
ELSE DATEADD ( HOUR , -6 , UTC.Field )
END
WHEN MONTH ( UTC.Field ) = 3
AND ( DATEPART ( WEEKDAY , UTC.Field ) + 7 ) <= DATEPART ( day , UTC.Field )
THEN
--According to the date, it's moved onto Summer daylight, but we need to account for the hours leading up to 2am if it's Sunday
CASE
WHEN DATEPART ( WEEKDAY , UTC.Field ) = 1
AND UTC.Field < '9:00'
--Before 9am UTC is before 2am Mountain so it's winter Daylight, -7 hours
THEN DATEADD ( HOUR , -7 , UTC.Field )
--Otherwise, it's summer daylight, -6 hours
ELSE DATEADD ( HOUR , -6 , UTC.Field )
END
--When it's November and the weekday is greater than the calendar date, it's still Summer so -6 from the time
WHEN MONTH ( UTC.Field ) = 11
AND DATEPART ( WEEKDAY , UTC.Field ) > DATEPART ( DAY , UTC.Field )
THEN DATEADD ( HOUR , -6 , UTC.Field )
WHEN MONTH ( UTC.Field ) = 11
AND DATEPART ( WEEKDAY , UTC.Field ) <= DATEPART ( DAY , UTC.Field )
--If the weekday is less than or equal to the calendar day it's Winter daylight but we need to account for the hours leading up to 2am.
CASE
WHEN DATEPART ( WEEKDAY , UTC.Field ) = 1
AND UTC.Field < '8:00'
--If it's before 8am UTC and it's Sunday in the logic outlined, then it's still Summer daylight, -6 hours
THEN DATEADD ( HOUR , -6 , UTC.Field )
--Otherwise, adjust for Winter daylight at -7
ELSE DATEADD ( HOUR , -7 , UTC.Field )
END
--If the date doesn't fall into any of the above logic, it's Winter daylight, -7
ELSE
DATEADD ( HOUR , -7 , UTC.Field )
END

小开

您必须重新格式化字符串以及转换为正确的时间。在这种情况下，我需要祖鲁时间。

Declare @Date datetime;
Declare @DateString varchar(50);
set @Date = GETDATE();
declare @ZuluTime datetime;


Declare @DateFrom varchar (50);
Declare @DateTo varchar (50);
set @ZuluTime = DATEADD(second, DATEDIFF(second, GETDATE(), GETUTCDATE()), @Date);
set @DateString =  FORMAT(@ZuluTime, 'yyyy-MM-ddThh:mm:ssZ', 'en-US' )
select @DateString;

小开

对甲骨文来说最好的方法:

硬编码的datetime:

SELECT TO_CHAR(CAST((FROM_TZ(CAST(TO_DATE('2018-10-27 21:00', 'YYYY-MM-DD HH24:MI') AS TIMESTAMP), 'UTC') AT  TIME ZONE 'EET') AS DATE), 'YYYY-MM-DD HH24:MI') UTC_TO_EET FROM DUAL

Result: 2018-10-28 00:00

列名和表名:

SELECT TO_CHAR(CAST((FROM_TZ(CAST(COLUMN_NAME AS TIMESTAMP), 'UTC') AT  TIME ZONE 'EET') AS DATE), 'YYYY-MM-DD HH24:MI') UTC_TO_EET FROM TABLE_NAME

小开

对于Azure SQL和@@Version >= SQL Server 2016用户，下面是一个使用AT TIME ZONE的简单函数。

CREATE FUNCTION [dbo].[Global_Convert_UTCTimeTo_LocalTime]
(
@LocalTimeZone        VARCHAR(50),
@UTCDateTime          DATETIME
)
RETURNS DATETIME
AS
BEGIN
DECLARE @ConvertedDateTime DATETIME;


SELECT @ConvertedDateTime = @UTCDateTime AT TIME ZONE 'UTC' AT TIME ZONE @LocalTimeZone
RETURN @ConvertedDateTime


END
GO

对于@LocalTimeZone可以接受的值类型，请转到这个链接或转到KEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Time Zones

小开

如果你的本地日期时间是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

时区名称的完整列表可以在以下地址找到:

SELECT * FROM sys.time_zone_info

是的，时区的命名很糟糕——即使它是Eastern Standard Time，也考虑到了夏令时。

小开

我有代码执行UTC到本地和本地到UTC时间，允许使用这样的代码进行转换

DECLARE @usersTimezone VARCHAR(32)='Europe/London'
DECLARE @utcDT DATETIME=GetUTCDate()
DECLARE @userDT DATETIME=[dbo].[funcUTCtoLocal](@utcDT, @usersTimezone)

而且

DECLARE @usersTimezone VARCHAR(32)='Europe/London'
DECLARE @userDT DATETIME=GetDate()
DECLARE @utcDT DATETIME=[dbo].[funcLocaltoUTC](@userDT, @usersTimezone)

这些函数可以支持NodaTime提供的IANA/TZDB中的全部或一部分时区-请参阅https://nodatime.org/TimeZones中的完整列表

请注意，我的用例意味着我只需要一个“当前”窗口，允许在大约+/- 5年的范围内转换时间。这意味着，如果您需要很宽的时间段，我使用的方法可能不适合您，因为它在给定日期范围内为每个时区间隔生成代码的方式不同。

该项目位于GitHub: https://github.com/elliveny/SQLServerTimeConversion上

这将根据这个例子生成SQL函数代码

小开

如果您将数据存储为UTC日期在数据库中，您可以做一些简单的事情

select
[MyUtcDate] + getdate() - getutcdate()
from [dbo].[mytable]

这是从服务器的角度来看，它总是本地的，你不会在AT TIME ZONE 'your time zone name'中摸索，如果您的数据库被移动到另一个时区，如客户端安装，硬编码的时区可能会咬你

小开

告诉服务器保存时间的时间，'utc'，然后要求它转换为特定的时区，在这种情况下是'巴西/东部'

quiz_step_progresses.created_at  at time zone 'utc' at time zone 'Brazil/East'

使用以下选择获取时区的完整列表;

select * from pg_timezone_names;

详情请点击这里。

https://popsql.com/learn-sql/postgresql/how-to-convert-utc-to-local-time-zone-in-postgresql

小开

对于任何仍然试图解决这个问题的人，这里有一个在SQL Server 2017中工作的概念证明

 declare
@StartDate date = '2020-01-01'


;with cte_utc as
(
select
1 as i
,CONVERT(datetime, @StartDate) AS UTC
,datepart(weekday, CONVERT(datetime, @StartDate)) as Weekday
,datepart(month, CONVERT(datetime, @StartDate)) as [Month]
,datepart(YEAR, CONVERT(datetime, @StartDate)) as [Year]
        

union all


Select
i + 1
,dateadd(d, 1, utc)
,datepart(weekday, CONVERT(datetime, dateadd(d, 1, utc))) as Weekday
,datepart(month, CONVERT(datetime, dateadd(d, 1, utc))) as [Month]
,datepart(YEAR, CONVERT(datetime, dateadd(d, 1, utc))) as [Year]
from
cte_utc
where
(i + 1) < 32767


), cte_utc_dates as
(
select
*,
DENSE_RANK()OVER(PARTITION BY [Year], [Month], [Weekday] ORDER BY Utc) WeekDayIndex
from
cte_utc


), cte_hours as (
select 0 as [Hour]
union all
select [Hour] + 1 from cte_hours where [Hour] < 23
)


select
d.*
, DATEADD(hour, h.Hour, d.UTC) AS UtcTime
,CONVERT(datetime, DATEADD(hour, h.Hour, d.UTC) AT TIME ZONE 'UTC' AT TIME ZONE 'Central Standard Time') CST
,CONVERT(datetime, DATEADD(hour, h.Hour, d.UTC) AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time') EST
from
cte_utc_dates d, cte_hours h
where
([Month] = 3 and [Weekday] = 1 and WeekDayIndex = 2 )-- dst start
or
([Month] = 11 and [Weekday] = 1 and WeekDayIndex = 1 )-- dst end
order by
utc
OPTION (MAXRECURSION 32767)


GO

小开

该函数将UTC时间转换为EST时间，并进行DST调整。你可以在这个函数中更改你设计的时区名称，或者从注册表中获取:

Create Function fnConvertUTCTimetoESTTime(
@UTCTime as datetime
)
returns datetime
as
begin
return convert(datetime, convert(varchar(23), @UTCTime AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time', 121), 121)
end
go


select dbo.fnConvertUTCTimetoESTTime ('2020-3-8 5:00:00.000')
, dbo.fnConvertUTCTimetoESTTime ('2020-3-8 6:00:00.000')
, dbo.fnConvertUTCTimetoESTTime ('2020-3-8 7:00:00.000')
, dbo.fnConvertUTCTimetoESTTime ('2020-3-8 8:00:00.000')


--returns 0:00am, 1:00am, 3:00am, 4:00am


select dbo.fnConvertUTCTimetoESTTime ('2020-11-1 4:00:00.000')
, dbo.fnConvertUTCTimetoESTTime ('2020-11-1 5:00:00.000')
, dbo.fnConvertUTCTimetoESTTime ('2020-11-1 6:00:00.000')
, dbo.fnConvertUTCTimetoESTTime ('2020-11-1 7:00:00.000')


--returns 0:00am, 1:00am, 1:00am, 2:00am

注意，您不能只返回"@UTCTime AT TIME ZONE 'UTC' AT TIME ZONE '东部标准时间'"作为结果，因为这个结果实际上是EST格式的UTC时间(当你比较这个"fake"EST时间或将其包含在order子句中，它将被转换回UTC时间)。

小开

最简单的答案并不总是在底部，但这次是，并且可以在上面的评论中看到。使用您自己的“AT TIME ZONE”来捕获列/数据字段的TzOffset，而不是当前的SYSDATETIME。在下面的数据中，2个查询，一个关于feb数据(DST是关闭的，在阿姆斯特丹的冬天)+1差异和阿姆斯特丹4月数据的第二次查询，因此+2小时差异。

    select top 2 month(receiveTimeUTC) as MonthInWinterOrSpring
,  receiveTimeUTC
,  CONVERT(datetime,  SWITCHOFFSET(CONVERT(datetimeoffset,  receiveTimeUTC),  DATENAME(TzOffset, SYSDATETIMEOFFSET()))) as LocalTimeWrongNoDST
,  CONVERT(datetime,  SWITCHOFFSET(CONVERT(datetimeoffset,  receiveTimeUTC),  DATENAME(TzOffset, receiveTimeUTC  AT TIME ZONE 'Central European Standard Time' ))) as LocalTimeWithDST
from sensordetails order by id


select top 2 month(receiveTimeUTC) as MonthInWinterOrSpring, receiveTimeUTC
,  CONVERT(datetime,  SWITCHOFFSET(CONVERT(datetimeoffset,  receiveTimeUTC),  DATENAME(TzOffset, SYSDATETIMEOFFSET()))) as LocalTimeWrongNoDST
,  CONVERT(datetime,  SWITCHOFFSET(CONVERT(datetimeoffset,  receiveTimeUTC),  DATENAME(TzOffset, receiveTimeUTC  AT TIME ZONE 'Central European Standard Time' ))) as LocalTimeWithDST
from sensordetails order by id desc

结果:

所以这是一个T-SQL (SQL Server Answer)，不需要函数的storedproc。