My<cod/>.net
Эффективное преобразование дат между UTC и локальными (т. е. PST) время в SQL 2005 г.
Что является лучшим способом, чтобы преобразовать UTC datetime в местных datetime. Это не так просто, как разница в getutcdate() и getdate(), потому что разница меняется в зависимости от того, что такое дата.
CLR интеграция для меня тоже не вариант.
Решение, которое я придумал для этой проблемы несколько месяцев назад, состояло в том, чтобы иметь таблицу летнего времени, хранящую начало и конец летнего времени в течение следующих 100 или около того лет, это решение казалось неэлегантным, но преобразования были быстрыми (простой поиск таблицы)
Создайте две таблицы и затем присоединитесь к ним, чтобы преобразовать сохраненные даты 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)
Если вы находитесь в US и заинтересованы только в переходе из UTC/GMT в фиксированный часовой пояс (например, EDT), этого кода должно быть достаточно. Я взбил его сегодня и считаю, что это правильно, но используйте на свой страх и риск.
Добавляет вычисляемый столбец в таблицу 'myTable', предполагая, что ваши даты находятся в столбце 'date'. Надеюсь, кто-то еще найдет это полезным.
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)
Гораздо более простое и универсальное решение, учитывающее переход на летнее время. Учитывая дату 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]