我正在做一些SQL选择查询,并希望将我的UTC日期时间列转换为本地时间,以便在我的查询结果中显示为本地时间。注意,我不希望通过代码进行这种转换,而是当我对我的数据库进行手动和随机SQL查询时。
当前回答
我发现当有大量数据时,一次性函数的方法太慢了。因此,我通过连接到一个允许计算小时差的表函数来实现它,它基本上是带有小时偏移量的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
其他回答
我有代码执行UTC到本地和本地到UTC时间,允许使用这样的代码进行转换
DECLARE @usersTimezone VARCHAR(32)='Europe/London'
DECLARE @utcDT DATETIME=GetUTCDate()
DECLARE @userDT DATETIME=[dbo].[funcUTCtoLocal](@utcDT, @usersTimezone)
and
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函数代码
你可以在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中创建夏令时开始和结束函数
该函数将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 'Eastern Standard TIME '”作为结果,因为该结果实际上是EST格式的UTC时间(当您比较这个“假”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。
对于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
推荐文章
- 如何在Ruby On Rails中使用NuoDB手动执行SQL命令
- 查询JSON类型内的数组元素
- 确定记录是否存在的最快方法
- 获得PostgreSQL数据库中当前连接数的正确查询
- 在SQL选择语句Order By 1的目的是什么?
- 从现有模式生成表关系图(SQL Server)
- 我如何循环通过一组记录在SQL Server?
- 数据库和模式的区别
- 如何在SQL Server中一次更改多个列
- 如何从命令行通过mysql运行一个查询?
- 外键约束可能导致循环或多条级联路径?
- 使用LIMIT/OFFSET运行查询,还可以获得总行数
- 当恢复sql时,psql无效命令\N
- 货币应该使用哪种数据类型?
- 如何选择每一行的列值不是独特的