Эффективное преобразование дат между временем UTC и местным (т. Е. PST) временем в SQL 2005

Каков наилучший способ конвертировать время в формате UTC в местное время? Это не так просто, как разница между getutcdate () и getdate (), потому что разница меняется в зависимости от даты.

Интеграция с CLR также не подходит для меня.

Решение, которое я придумал для этой проблемы несколько месяцев назад, заключалось в том, чтобы иметь таблицу летнего времени, в которой хранятся данные о начале и конце летнего времени в течение следующих примерно 100 лет, это решение казалось неэффективным, но преобразования были быстрыми (просто поиск таблицы)

24.08.2008 02:08:15
12 ОТВЕТОВ
РЕШЕНИЕ

Создайте две таблицы и затем присоединитесь к ним, чтобы преобразовать сохраненные даты по Гринвичу в местное время:

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)
28
24.08.2008 14:17:17
Поскольку dateadd принимает только целые числа, я сохранил смещение в минутах, чтобы справиться со смещениями, такими как 9.5.
Simon 14.12.2010 00:24:36

Сохраняйте таблицу TimeZone или используйте расширенный сохраненный процесс (xp_cmdshell или COM-компонент или ваш собственный) и попросите ОС сделать это. Если вы идете по маршруту xp, вы, вероятно, захотите кэшировать смещение на день.

0
24.08.2008 13:28:11

Если любая из этих проблем затрагивает вас, вы никогда не должны хранить местное время в базе данных:

  1. DST заключается в том, что вокруг периода замедления существует «час неопределенности», когда местное время не может быть однозначно преобразовано. Если требуются точные даты и время, храните в UTC.
  2. Если вы хотите показывать пользователям дату и время в своем часовом поясе, а не в часовом поясе, в котором происходило действие, сохраните их в формате UTC.
5
24.08.2008 20:45:26
Или сохранить местное время и смещение часового пояса? Предполагая, что вы можете зафиксировать смещение часового пояса при захвате времени, это однозначно. Если вам известно смещение часового пояса, применяемое при захвате времени, вы всегда можете преобразовать его в UTC и полезно, если вы хотите показать диапазон времени в разных зонах (например, вместо того, чтобы преобразовывать все в местный часовой пояс). Я продолжаю слышать, что лучше хранить в UTC, но для меня это выбрасывание информации.
Robin Minto 23.05.2009 07:54:07
Ну как насчет хранения UTC плюс смещение часового пояса?
keuleJ 4.09.2017 15:48:32

Если вы находитесь в США и заинтересованы только в переходе с 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)
15
8.05.2009 04:01:08
Не ответ на актуальный вопрос, но он решил мою проблему. Спасибо!!
Bill 20.05.2009 19:51:30
Рад слышать, что кто-то еще нашел это полезным! Вы правы, что это не решение общей проблемы, которая была поставлена, но она должна быть правильной для большинства мест в США, где все мои серверы расположены.
Bob Albright 21.05.2009 03:14:06
Спасибо, что поделились этим кодом. Это спасло меня от утренней математики.
Erik Giberti 16.08.2012 15:58:21
Извините за налет через много лет, но ... мне кажется, что у вас есть переходы, происходящие в 0200Z, но они происходят в 0100EST / 0200EDT, то есть 0600Z. В остальном это выглядит довольно хорошо для меня, я использую его, чтобы исправить недостаток предвидения, смешивая локальные и UTC-марки в моей базе данных. По крайней мере, мои серверы и пользователи находятся в одной TZ.
MattW 29.01.2015 20:57:21

В ответе Эрика З. Борода следующий 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 это приведет к тому, что время 2:00 НЕ будет преобразовано в 1:00. Конечно, вероятность того, что время в 2 часа ночи точно, мала, но это может произойти, и это приведет к неверному преобразованию.

5
23.05.2017 12:34:51
Ларри - ты сослался на «Ответ 7». Я думаю, вы, возможно, имеете в виду ответ Эрика З. Борода? 7, на которые вы ссылаетесь, является репутацией и, вероятно, изменится.
Robin Minto 1.11.2010 17:22:10
Заменено на ссылку для ответа.
Aidan Ryan 25.03.2011 19:28:09

Только для чтения Используйте это (вдохновленный неправильным решением Боба Олбрайта ):

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 (), но мои изменения были отклонены несколько раз. Я попытался объяснить, но был отклонен как нуб. Это отличный подход к проблеме! Это заставило меня начать в правильном направлении. Я ненавижу создавать этот отдельный ответ, когда его просто нужно немного подправить, но я попытался ¯ \ _ (ツ) _ / ¯

8
23.05.2017 11:47:35
Спасибо, что поделились Уиллом. Я, например, могу признать, что в силу этого ответа вы ЯВНО не новичок.
pimbrouwers 9.02.2018 18:50:58

Гораздо более простое и универсальное решение, которое учитывает переход на летнее время. Учитывая дату 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]
7
23.07.2014 06:11:56
Не уверен, что этот код правильный. Он получает время, отличное от этого момента (включая DST), и применяет его к YourDate, который может иметь или не иметь тот же DST с этим моментом.
Sheepy 11.04.2014 05:14:10
Я обновил скрипт выше. «YourUtcDateHere» предназначен для типа данных DateTime, к которому не применено смещение UTC.
MikeTeeVee 23.07.2014 06:21:35
Помните, что это решение зависит от часового пояса сервера, на котором установлен SQL Server, который может отличаться от часового пояса пользователя или приложения.
Dinei 20.10.2015 11:12:23
Шипи прав. Ответ не учитывает переход на летнее время, о котором упоминалось. Ответ @Daniel & MikeTeevee работает только в двух случаях: 1) если выполняется во время летнего времени, а запрашиваемый dateTime - во время летнего времени ... или 2) выполняется вне DST, а запрашиваемый dateTime вне летнего времени. То есть это будет неправильно в двух других случаях: 1) запрос выполняется во время летнего времени, а запрашиваемая дата - не во время летнего времени, и 2) «текущее время» - не во время летнего времени, а запрашиваемый dateTime - во время летнего времени - см. мое решение учесть это stackoverflow.com/a/16994483/1241580 ... которое относится к восточному времени
WillDeStijl 9.05.2018 01:20:01

Мне нравится ответ @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)
0
23.05.2017 10:29:52

Я прочитал много сообщений StackOverflow по этой проблеме и нашел много методов. Какой-то "вид" ок. Я также нашел ссылку на MS ( https://msdn.microsoft.com/en-us/library/mt612795.aspx ), которую я пытался использовать в своем сценарии. Мне удалось достичь требуемого результата, НО я не уверен, будет ли это работать на версии 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')
0
26.04.2016 15:36:11
По вашей ссылке AT TIME ZONEтолько функция SQL 2016+
Ron DeFreitas 26.08.2016 19:07:29
эти ответы о том, что DIFF текущего местного времени и UTC являются правильными ИНОГДА, но не всегда - в зависимости от КОГДА выполнено! Разница, которая должна применяться к записям вашей БД, зависит от того, находится ли она во время DST или нет - которая может отличаться от вашего текущего статуса DST при выполнении: [ stackoverflow.com/questions/24797/…
WillDeStijl 6.03.2019 18:51:52

Я использую это, потому что все мои даты отныне вперед.

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

Для исторических дат (или для обработки будущих изменений в DST, я предполагаю, что решение Боба Олбрайта было бы подходящим вариантом).

Модификация, которую я делаю в своем коде, заключается в использовании целевого столбца:

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

Пока что это похоже на работу, но я рад получить отзывы.

0
15.08.2016 18:20:49

Вот код, который я использую для составления таблицы часовых поясов. Это немного наивно, но обычно достаточно хорошо.

Предположения:

  1. Предполагается, что действуют только правила США (летнее время - 2 часа ночи в какое-то заранее определенное воскресенье и т. Д.).
  2. Предполагается, что у вас нет дат до 1970
  3. Предполагается, что вы знаете местные смещения часового пояса (т.е. 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
0
28.01.2017 01:10:14
--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
0
24.04.2019 04:31:06