我正在做一些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日期在数据库中,您可以做一些简单的事情
select
[MyUtcDate] + getdate() - getutcdate()
from [dbo].[mytable]
这是从服务器的角度来看,它总是本地的,你不会笨拙地使用AT TIME ZONE '你的时区名称', 如果您的数据库像客户端安装一样被移动到另一个时区,那么硬编码的时区可能会让您感到困扰。
作为一个警告-如果你要使用以下(注意毫秒而不是分钟):
SELECT DATEADD(ms, DATEDIFF(ms, GETUTCDATE(), GETDATE()), MyTable.UtcColumn)
AS ColumnInLocalTime
FROM MyTable
请记住,DATEDIFF部分并不总是返回相同的数字。所以不要用它来将DateTimes精确到毫秒。
如果你的本地日期时间是东部标准时间,你想从UTC转换为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
是的,时区的命名很糟糕——即使是东部标准时间,也考虑到了夏令时。
你可以在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中创建夏令时开始和结束函数
如果你需要一个转换而不是你的服务器位置,这里有一个函数,允许你传递一个标准偏移量,并考虑美国夏令时:
-- =============================================
-- 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
推荐文章
- 如何在Ruby On Rails中使用NuoDB手动执行SQL命令
- 查询JSON类型内的数组元素
- 确定记录是否存在的最快方法
- 获得PostgreSQL数据库中当前连接数的正确查询
- 在SQL选择语句Order By 1的目的是什么?
- 从现有模式生成表关系图(SQL Server)
- 我如何循环通过一组记录在SQL Server?
- 数据库和模式的区别
- 如何在SQL Server中一次更改多个列
- 如何从命令行通过mysql运行一个查询?
- 外键约束可能导致循环或多条级联路径?
- 使用LIMIT/OFFSET运行查询,还可以获得总行数
- 当恢复sql时,psql无效命令\N
- 货币应该使用哪种数据类型?
- 如何选择每一行的列值不是独特的