在SQL Server中将GMT转换为EST

Question

我有一个存储夏令时和非夏令时记录的表

CREATE TABLE #tmp 
(
ID int,
IntervalName nvarchar(100),
StartDate datetime,
EndDate Datetime,
offset numeric(5, 2)
)
INSERT INTO #tmp 
VALUES (1, 'EDT', '2022-03-13 07:00:00.000', '2022-11-06 06:00:00.000',-4.00)
INSERT INTO #tmp 
VALUES (2, 'EST', '2022-11-06 06:00:00.000', '2023-03-12 07:00:00.000', -5.00)

我有我的事务表，Date列在GMT时区。

ID   DatetimeGMT                  DatetimeLocal  
---------------------------------------------------------
1    2022-11-05 07:00:00.000      2022-11-05 03:00:00.000        
2    2022-11-10 06:00:00.000      2023-11-10 01:00:00.000 

现在我的DatetimeLocal列正在计算基于DatetimeGMT列的偏移小时。

ID = 1所在行偏移量为-4,ID = 2所在行偏移量为-5。

有什么建议我们可以做到这一点吗?

Answer 1

如果所有的偏移量都存储在这样的表中，那么您只需要JOIN就可以找到正确的偏移量

如

drop table if exists #tmp
drop table if exists #tran
go
CREATE TABLE #tmp 
(
ID int,
IntervalName nvarchar(100),
StartDate datetime,
EndDate Datetime,
offset numeric(5, 2)
)
INSERT INTO #tmp 
VALUES (1, 'EDT', '2022-03-13 07:00:00.000', '2022-11-06 06:00:00.000',-4.00)
INSERT INTO #tmp 
VALUES (2, 'EST', '2022-11-06 06:00:00.000', '2023-03-12 07:00:00.000', -5.00)
create table #tran(id int primary key, DateTimeUTC datetime)
insert into #tran(id,DateTimeUTC) values (1,'2022-11-05 07:00:00.000'),(2,'2022-11-10 06:00:00.000')

select t.id, t.DateTimeUTC, dateadd(hour,offset,t.DateTimeUTC) DateTimeLocal, tz.offset
from #tran t
join #tmp tz
on t.DateTimeUTC >= tz.StartDate
and t.DateTimeUTC < tz.EndDate

输出

id          DateTimeUTC             DateTimeLocal           offset
----------- ----------------------- ----------------------- ---------------------------------------
1           2022-11-05 07:00:00.000 2022-11-05 03:00:00.000 -4.00
2           2022-11-10 06:00:00.000 2022-11-10 01:00:00.000 -5.00

SQL Server 2016及以后版本内置了偏移量，所以你可以编写像

这样的查询

select t.id, 
t.DateTimeUTC, 
cast(t.DateTimeUTC at time zone 'UTC' at time zone 'EASTERN STANDARD TIME' as datetime) DateTimeLocal
from #tran t