我正在做一些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 '你的时区名称', 如果您的数据库像客户端安装一样被移动到另一个时区,那么硬编码的时区可能会让您感到困扰。
您必须重新格式化字符串以及转换为正确的时间。在这种情况下,我需要祖鲁时间。
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分段。一年是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
作为一个警告-如果你要使用以下(注意毫秒而不是分钟):
SELECT DATEADD(ms, DATEDIFF(ms, GETUTCDATE(), GETDATE()), MyTable.UtcColumn)
AS ColumnInLocalTime
FROM MyTable
请记住,DATEDIFF部分并不总是返回相同的数字。所以不要用它来将DateTimes精确到毫秒。
对于任何仍然试图解决这个问题的人,这里有一个在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
推荐文章
- 如何在Ruby On Rails中使用NuoDB手动执行SQL命令
- 查询JSON类型内的数组元素
- 确定记录是否存在的最快方法
- 获得PostgreSQL数据库中当前连接数的正确查询
- 在SQL选择语句Order By 1的目的是什么?
- 从现有模式生成表关系图(SQL Server)
- 我如何循环通过一组记录在SQL Server?
- 数据库和模式的区别
- 如何在SQL Server中一次更改多个列
- 如何从命令行通过mysql运行一个查询?
- 外键约束可能导致循环或多条级联路径?
- 使用LIMIT/OFFSET运行查询,还可以获得总行数
- 当恢复sql时,psql无效命令\N
- 货币应该使用哪种数据类型?
- 如何选择每一行的列值不是独特的