在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)

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

   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

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

例如

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

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