Эффективно Преобразование дат между UTC и Local (т.е. PST) в SQL 2005
Каков наилучший способ конвертировать дату и время UTC в локальное datetime. Это не так просто, как разница getutcdate() и getdate(), потому что разница изменяется в зависимости от даты.
Интеграция CLR также не является для меня вариантом.
Решение, которое я разработал для этой проблемы несколько месяцев назад, состояло в том, чтобы иметь таблицу времени, которая хранила начальные и конечные дневные сберегательные дни в течение следующих 100 или около того лет, это решение казалось неэлегантным, но конверсии были быстрый (простой поиск в таблице)
Ответы
Ответ 1
Создайте две таблицы и затем присоедините их к ним, чтобы конвертировать сохраненные даты GMT в локальное время:
TimeZones e.g.
--------- ----
TimeZoneId 19
Name Eastern (GMT -5)
Offset -5
Создайте таблицу дневного времени и заполните ее как можно больше информации (локальные законы меняются все время, поэтому нет способа предсказать, какие данные будут выглядеть годами в будущем)
DaylightSavings
---------------
TimeZoneId 19
BeginDst 3/9/2008 2:00 AM
EndDst 11/2/2008 2:00 AM
Присоединяйтесь к ним следующим образом:
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
Преобразование таких дат:
dateadd(hh, tz.Offset +
case when ds.LocalTimeZone is not null
then 1 else 0 end, TheDateToConvert)
Ответ 2
Если вы находитесь в США и только заинтересованы в переходе от UTC/GMT к фиксированному часовому поясу (например, EDT), этого кода должно быть достаточно. Я взломал его сегодня и считаю правильным, но использую на свой страх и риск.
Добавляет вычисленный столбец в таблицу "myTable", если ваши даты указаны в столбце "дата". Надеюсь, что кто-то другой найдет это полезным.
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)
Ответ 3
Более простое и общее решение, учитывающее экономию времени. Учитывая дату UTC в "YourDateHere":
--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]
Ответ 4
Если любой из этих проблем влияет на вас, вы никогда не должны сохранять локальные времена в базе данных:
- С DST заключается в том, что в течение периода отставания существует "час неопределенности", когда местное время не может быть однозначно преобразовано. Если требуются точные даты и время, сохраните их в UTC.
- Если вы хотите показывать пользователям дату и время в своем часовом поясе, а не часовой пояс, в котором произошло это действие, сохраните его в UTC.
Ответ 5
В Eric Z Beard ответьте, следующий SQL
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
может быть более точно:
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 "между" включен. В конце DST это приведет к тому, что время 2AM не будет преобразовано в 1AM. Конечно, вероятность того, что время 2AM точно мала, но это может произойти, и это приведет к неверному преобразованию.
Ответ 6
ТОЛЬКО ДЛЯ ПРОЧИТАНИЯ Используйте это (с учетом неправильного решения Боба Олбрайт):
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
Я отправил этот ответ после того, как попытался отредактировать неверный ответ Боба Олбрайт. Я исправил время и удалил лишнее abs(), но мои изменения были отклонены несколько раз. Я попытался объяснить, но был отклонен как нуб. Его БОЛЬШОЙ подход к проблеме! Это заставило меня начать в правильном направлении. Мне не нравится создавать этот отдельный ответ, когда ему просто нужна небольшая настройка, но я попробовал ¯\_ (ツ) _/¯
Ответ 7
Ведите таблицу TimeZone или обходите с расширенным хранимым proc (xp_cmdshell или COM-компонентом или вашим собственным) и попросите ОС сделать это. Если вы отправите маршрут xp, вы, вероятно, захотите кэшировать смещение на один день.
Ответ 8
Мне нравится answer @Eric Z Beard.
Однако, чтобы избежать выполнения соединения каждый раз, что об этом?
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
Тогда
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)
наконец, становясь
dateadd(hh, @offset, TheDateToConvert)
Ответ 9
Я прочитал много сообщений StackOverflow по этой проблеме и нашел много методов. Некоторые "вроде" хорошо. Я также нашел эту ссылку для MS (https://msdn.microsoft.com/en-us/library/mt612795.aspx), которую я пытался использовать в своем script. Мне удалось достичь требуемого результата, но я не уверен, что это будет работать в версии 2005 года. В любом случае, я надеюсь, что это поможет.
Fnc для возврата PST из системы UTC по умолчанию
CREATE FUNCTION dbo.GetPst()
RETURNS DATETIME
AS
BEGIN
RETURN SYSDATETIMEOFFSET() AT TIME ZONE 'Pacific Standard Time'
END
SELECT dbo.GetPst()
Fnc, чтобы вернуть PST из предоставленной временной метки
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')
Ответ 10
Я использую это, потому что все мои даты уже сейчас.
DATEADD(HH,(DATEPART(HOUR, GETUTCDATE())-DATEPART(HOUR, GETDATE()))*-1, GETDATE())
Для исторических дат (или для обработки будущих изменений в DST, я предполагаю, что решение Боба Олбрайт было бы правильным путем.
Модификация, которую я делаю для моего кода, - использовать целевой столбец:
DATEADD(HH,(DATEPART(HOUR, GETUTCDATE())-DATEPART(HOUR, GETDATE()))*-1, [MySourceColumn])
Пока это работает, но я рад получить обратную связь.
Ответ 11
Вот код, который я использую для создания моей таблицы часовых поясов. Это немного наивно, но обычно это достаточно хорошо.
Предположения:
- Он принимает только US-правила (DST - 2AM на некоторое предопределенное воскресенье,
и т.д.).
- Предполагается, что у вас нет дат до 1970 года.
- Предполагается, что вы знаете смещения локального часового пояса (то есть: EST = -05: 00, EDT = -04: 00 и т.д.).
Здесь SQL:
-- 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
