关于SQL Server：在SQL 2005中有效地在UTC和本地(即PST)时间之间转换日期

Effectively Converting dates between UTC and Local (ie. PST) time in SQL 2005

将UTC日期时间转换为本地日期时间的最佳方法是什么？它不像getutcdate()和getdate()一样简单，因为差异取决于日期是什么。

CLR集成也不是我的选择。

我几个月前针对这个问题提出的解决方案是拥有一个日光节约时间表，该表存储了未来100年左右的开始和结束的日光节约时间，该解决方案看起来不算什么，但是转换很快(简单表查询)

创建两个表，然后加入其中，以将存储的GMT日期转换为本地时间：

1
2
3
4
5

TimeZones e.g.
--------- ----
TimeZoneId 19
Name Eastern (GMT -5)
Offset -5

创建夏令时表并向其填充尽可能多的信息(当地法律一直在变化，因此无法预测未来几年的数据情况)

1
2
3
4
5

DaylightSavings
---------------
TimeZoneId 19
BeginDst 3/9/2008 2:00 AM
EndDst 11/2/2008 2:00 AM

像这样加入他们：

1
2
3

inner join TimeZones tz on x.TimeZoneId=tz.TimeZoneId
left join DaylightSavings ds on tz.TimeZoneId=ds.LocalTimeZone
and x.TheDateToConvert between ds.BeginDst and ds.EndDst

像这样转换日期：

1
2
3

dateadd(hh, tz.Offset +
case when ds.LocalTimeZone is not null
then 1 else 0 end, TheDateToConvert)

如果您在美国，并且仅对从UTC / GMT到固定时区(例如EDT)感兴趣，则此代码就足够了。我今天整理了一下，并认为它是正确的，但后果自负。

假设您的日期在"日期"列上，则将计算列添加到表" myTable"中。希望其他人觉得这有用。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

ALTER TABLE myTable ADD date_edt AS
dateadd(hh,
-- The schedule through 2006 in the United States was that DST began on the first Sunday in April
-- (April 2, 2006), and changed back to standard time on the last Sunday in October (October 29, 2006).
-- The time is adjusted at 02:00 local time.
CASE WHEN YEAR(date) <= 2006 THEN
CASE WHEN
date >= '4/' + CAST(abs(8-DATEPART(dw,'4/1/' + CAST(YEAR(date) as varchar)))%7 + 1 as varchar) + '/' + CAST(YEAR(date) as varchar) + ' 2:00'
AND
date < '10/' + CAST(32-DATEPART(dw,'10/31/' + CAST(YEAR(date) as varchar)) as varchar) + '/' + CAST(YEAR(date) as varchar) + ' 2:00'
THEN -4 ELSE -5 END
ELSE
-- By the Energy Policy Act of 2005, daylight saving time (DST) was extended in the United States in 2007.
-- DST starts on the second Sunday of March, which is three weeks earlier than in the past, and it ends on
-- the first Sunday of November, one week later than in years past. This change resulted in a new DST period
-- that is four weeks (five in years when March has five Sundays) longer than in previous years.[35] In 2008
-- daylight saving time ended at 02:00 on Sunday, November 2, and in 2009 it began at 02:00 on Sunday, March 8.[36]
CASE WHEN
date >= '3/' + CAST(abs(8-DATEPART(dw,'3/1/' + CAST(YEAR(date) as varchar)))%7 + 8 as varchar) + '/' + CAST(YEAR(date) as varchar) + ' 2:00'
AND
date <
'11/' + CAST(abs(8-DATEPART(dw,'11/1/' + CAST(YEAR(date) as varchar)))%7 + 1 as varchar) + '/' + CAST(YEAR(date) as varchar) + ' 2:00'
THEN -4 ELSE -5 END
END
,date)

一种考虑日光节约的简单得多且通用的解决方案。在" YourDateHere"中给定UTC日期：

1
2
3
4
5

--Use Minutes ("MI") here instead of hours because sometimes
-- the UTC offset may be half an hour (e.g. 9.5 hours).
SELECT DATEADD(MI,
DATEDIFF(MI, SYSUTCDATETIME(),SYSDATETIME()),
YourUtcDateHere)[LocalDateTime]

仅供只读使用(受Bob Albright的错误解决方案启发)：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

SELECT
date1,
dateadd(hh,
-- The schedule through 2006 in the United States was that DST began on the first Sunday in April
-- (April 2, 2006), and changed back to standard time on the last Sunday in October (October 29, 2006).
-- The time is adjusted at 02:00 local time (which, for edt, is 07:00 UTC at the start, and 06:00 GMT at the end).
CASE WHEN YEAR(date1) <= 2006 THEN
CASE WHEN
date1 >= '4/' + CAST((8-DATEPART(dw,'4/1/' + CAST(YEAR(date1) as varchar)))%7 + 1 as varchar) + '/' + CAST(YEAR(date1) as varchar) + ' 7:00'
AND
date1 < '10/' + CAST(32-DATEPART(dw,'10/31/' + CAST(YEAR(date1) as varchar)) as varchar) + '/' + CAST(YEAR(date1) as varchar) + ' 6:00'
THEN -4 ELSE -5 END
ELSE
-- By the Energy Policy Act of 2005, daylight saving time (DST) was extended in the United States in 2007.
-- DST starts on the second Sunday of March, which is three weeks earlier than in the past, and it ends on
-- the first Sunday of November, one week later than in years past. This change resulted in a new DST period
-- that is four weeks (five in years when March has five Sundays) longer than in previous years. In 2008
-- daylight saving time ended at 02:00 edt (06:00 UTC) on Sunday, November 2, and in 2009 it began at 02:00 edt (07:00 UTC) on Sunday, March 8
CASE WHEN
date1 >= '3/' + CAST((8-DATEPART(dw,'3/1/' + CAST(YEAR(date1) as varchar)))%7 + 8 as varchar) + '/' + CAST(YEAR(date1) as varchar) + ' 7:00'
AND
date1 < '11/' + CAST((8-DATEPART(dw,'11/1/' + CAST(YEAR(date1) as varchar)))%7 + 1 as varchar) + '/' + CAST(YEAR(date1) as varchar) + ' 6:00'
THEN -4 ELSE -5 END
END
, date1) as date1Edt
from MyTbl

在尝试编辑Bob Albright的错误答案后，我发布了此答案。我更正了时间并删除了多余的abs()，但是我的编辑多次被拒绝。我试图解释，但被视为菜鸟。他是解决问题的绝妙方法！它使我朝着正确的方向开始。当他只需要稍作调整时，我不想创建这个单独的答案，但是我尝试了ˉ _(ツ)_ /ˉ

在Eric Z Beard的答案中，以下SQL

1
2
3

inner join TimeZones tz on x.TimeZoneId=tz.TimeZoneId
left join DaylightSavings ds on tz.TimeZoneId=ds.LocalTimeZone
and x.TheDateToConvert between ds.BeginDst and ds.EndDst

可能更准确地说是：

1
2
3

inner join TimeZones tz on x.TimeZoneId=tz.TimeZoneId
left join DaylightSavings ds on tz.TimeZoneId=ds.LocalTimeZone
and x.TheDateToConvert >= ds.BeginDst and x.TheDateToConvert < ds.EndDst

(以上代码未经测试)

这样做的原因是sql" between"语句是包含在内的。在DST的后端，这将导致2AM时间未转换为1AM。当然，恰好是2AM的可能性很小，但是可能会发生，并且会导致无效的转换。

如果这些问题之一影响您，则永远不要在数据库中存储本地时间：

DST的特点是在回退期周围存在"不确定的小时"，在该回退期中，本地时间无法明确转换。如果需要确切的日期和时间，则存储在UTC中。

如果要向用户显示他们自己的时区中的日期和时间，而不是执行操作的时区，请存储在UTC中。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

--Adapted Bob Albright and WillDeStijl suggestions for SQL server 2014
--
--In this instance I had no dates prior to 2006, therefore I simplified the case example
--I had to add the variables for the assignment to allow trimming the timestamp from my resultset

DECLARE @MARCH_DST as DATETIME
SET @MARCH_DST='3/' + CAST((8-DATEPART(dw,'3/1/' + CAST(YEAR(getdate()) as varchar)))%7 + 8 as varchar) + '/' + CAST(YEAR(getdate()) as varchar) + ' 7:00'

DECLARE @NOV_DST as DATETIME
SET @NOV_DST='11/' + CAST((8-DATEPART(dw,'11/1/' + CAST(YEAR(getdate()) as varchar)))%7 + 1 as varchar) + '/' + CAST(YEAR(getdate()) as varchar) + ' 6:00'

select cast(dateadd(HOUR,
-- By the Energy Policy Act of 2005, daylight saving time (DST) was extended in the United States in 2007.
-- DST starts on the second Sunday of March, which is three weeks earlier than in the past, and it ends on
-- the first Sunday of November, one week later than in years past. This change resulted in a new DST period
-- that is four weeks (five in years when March has five Sundays) longer than in previous years. In 2008
-- daylight saving time ended at 02:00 edt (06:00 UTC) on Sunday, November 2, and in 2009 it began at 02:00 edt (07:00 UTC) on Sunday, March 8
CASE WHEN
date1 >=@MARCH_DST
AND
date1< @NOV_DST
THEN -4 ELSE -5 END
, date1) as DATE) as date1_edited

这是我用来制作时区表的代码。这有点天真，但通常足够好。

假设：

假设仅美国使用规则(DST在某些预定义的星期日凌晨2点，
等等)。

假设您没有1970年之前的日期

假设您知道本地时区偏移量(即EST = -05：00，EDT = -04：00等)

这是SQL：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139

-- make a table (#dst) of years 1970-2101. Note that DST could change in the future and
-- everything was all custom and jacked before 1970 in the US.
declare @first_year varchar(4) = '1970'
declare @last_year varchar(4) = '2101'

-- make a table of all the years desired
if object_id('tempdb..#years') is not null drop table #years
;with cte as (
select cast(@first_year as int) as int_year
,@first_year as str_year
,cast(@first_year + '-01-01' as datetime) as start_of_year
union all
select int_year + 1
,cast(int_year + 1 as varchar(4))
,dateadd(year, 1, start_of_year)
from cte
where int_year + 1 <= @last_year
)
select *
into #years
from cte
option (maxrecursion 500);

-- make a staging table of all the important DST dates each year
if object_id('tempdb..#dst_stage') is not null drop table #dst_stage
select dst_date
,time_period
,int_year
,row_number() over (order by dst_date) as ordinal
into #dst_stage
from (
-- start of year
select y.start_of_year as dst_date
,'start of year' as time_period
,int_year
from #years y

union all
select dateadd(year, 1, y.start_of_year)
,'start of year' as time_period
,int_year
from #years y
where y.str_year = @last_year

-- start of dst
union all
select
case
when y.int_year >= 2007 then
-- second sunday in march
dateadd(day, ((7 - datepart(weekday, y.str_year + '-03-08')) + 1) % 7, y.str_year + '-03-08')
when y.int_year between 1987 and 2006 then
-- first sunday in april
dateadd(day, ((7 - datepart(weekday, y.str_year + '-04-01')) + 1) % 7, y.str_year + '-04-01')
when y.int_year = 1974 then
-- special case
cast('1974-01-06' as datetime)
when y.int_year = 1975 then
-- special case
cast('1975-02-23' as datetime)
else
-- last sunday in april
dateadd(day, ((7 - datepart(weekday, y.str_year + '-04-24')) + 1) % 7, y.str_year + '-04-24')
end
,'start of dst' as time_period
,int_year
from #years y

-- end of dst
union all
select
case
when y.int_year >= 2007 then
-- first sunday in november
dateadd(day, ((7 - datepart(weekday, y.str_year + '-11-01')) + 1) % 7, y.str_year + '-11-01')
else
-- last sunday in october
dateadd(day, ((7 - datepart(weekday, y.str_year + '-10-25')) + 1) % 7, y.str_year + '-10-25')
end
,'end of dst' as time_period
,int_year
from #years y
) y
order by 1

-- assemble a final table
if object_id('tempdb..#dst') is not null drop table #dst
select a.dst_date +
case
when a.time_period = 'start of dst' then ' 03:00'
when a.time_period = 'end of dst' then ' 02:00'
else ' 00:00'
end as start_date
,b.dst_date +
case
when b.time_period = 'start of dst' then ' 02:00'
when b.time_period = 'end of dst' then ' 01:00'
else ' 00:00'
end as end_date
,cast(case when a.time_period = 'start of dst' then 1 else 0 end as bit) as is_dst
,cast(0 as bit) as is_ambiguous
,cast(0 as bit) as is_invalid
into #dst
from #dst_stage a
join #dst_stage b on a.ordinal + 1 = b.ordinal
union all
select a.dst_date + ' 02:00' as start_date
,a.dst_date + ' 03:00' as end_date
,cast(1 as bit) as is_dst
,cast(0 as bit) as is_ambiguous
,cast(1 as bit) as is_invalid
from #dst_stage a
where a.time_period = 'start of dst'
union all
select a.dst_date + ' 01:00' as start_date
,a.dst_date + ' 02:00' as end_date
,cast(0 as bit) as is_dst
,cast(1 as bit) as is_ambiguous
,cast(0 as bit) as is_invalid
from #dst_stage a
where a.time_period = 'end of dst'
order by 1

-------------------------------------------------------------------------------

-- Test Eastern
select
the_date as eastern_local
,todatetimeoffset(the_date, case when b.is_dst = 1 then '-04:00' else '-05:00' end) as eastern_local_tz
,switchoffset(todatetimeoffset(the_date, case when b.is_dst = 1 then '-04:00' else '-05:00' end), '+00:00') as utc_tz
--,b.*
from (
select cast('2015-03-08' as datetime) as the_date
union all select cast('2015-03-08 02:30' as datetime) as the_date
union all select cast('2015-03-08 13:00' as datetime) as the_date
union all select cast('2015-11-01 01:30' as datetime) as the_date
union all select cast('2015-11-01 03:00' as datetime) as the_date
) a left join
#dst b on b.start_date <= a.the_date and a.the_date < b.end_date

我之所以使用它，是因为我的所有约会都是从现在开始。

1	DATEADD(HH,(DATEPART(HOUR, GETUTCDATE())-DATEPART(HOUR, GETDATE()))*-1, GETDATE())

对于历史日期(或处理DST的将来更改)，我想Bob Albright的解决方案将是正确的选择。

我对代码进行的修改是使用目标列：

1	DATEADD(HH,(DATEPART(HOUR, GETUTCDATE())-DATEPART(HOUR, GETDATE()))*-1, [MySourceColumn])

到目前为止，这似乎可行，但是我很高兴收到反馈。

我已经阅读了很多有关此问题的StackOverflow帖子，并发现了许多方法。有点"不错"。我还找到了我尝试在脚本中使用的该MS参考(https://msdn.microsoft.com/zh-cn/library/mt612795.aspx)。我已经设法达到了所需的结果，但是我不确定这是否可以在2005版上运行。无论哪种方式，我都希望能有所帮助。

Fnc从系统UTC默认值返回PST

1
2
3
4
5
6
7
8
9
10

CREATE FUNCTION dbo.GetPst()
RETURNS DATETIME
AS
BEGIN

RETURN SYSDATETIMEOFFSET() AT TIME ZONE 'Pacific Standard Time'

END

SELECT dbo.GetPst()

Fnc从提供的时间戳返回PST

1
2
3
4
5
6
7
8
9
10
11

CREATE FUNCTION dbo.ConvertUtcToPst(@utcTime DATETIME)
RETURNS DATETIME
AS
BEGIN

RETURN DATEADD(HOUR, 0 - DATEDIFF(HOUR, CAST(SYSDATETIMEOFFSET() AT TIME ZONE 'Pacific Standard Time' AS DATETIME), SYSDATETIME()), @utcTime)

END

SELECT dbo.ConvertUtcToPst('2016-04-25 22:50:01.900')

我喜欢@Eric Z Beard提供的答案。

但是，为避免每次都执行联接，该怎么办？

1
2
3
4
5
6
7
8
9
10
11
12
13
14

TimeZoneOffsets
---------------
TimeZoneId 19
Begin 1/4/2008 2:00 AM
End 1/9/2008 2:00 AM
Offset -5
TimeZoneId 19
Begin 1/9/2008 2:00 AM
End 1/4/2009 2:00 AM
Offset -6
TimeZoneId 20 --Hong Kong for example - no DST
Begin 1/1/1900
End 31/12/9999
Offset +8

然后

1
2
3

Declare @offset INT = (Select IsNull(tz.Offset,0) from YourTable ds
join TimeZoneOffsets tz on tz.TimeZoneId=ds.LocalTimeZoneId
and x.TheDateToConvert >= ds.Begin and x.TheDateToConvert < ds.End)

终于成为