Effettivamente Conversione delle date tra ora UTC e locale (es. PST) in SQL 2005

Qual è il modo migliore per convertire un datetime UTC in datetime locale. Non è semplice come una differenza getutcdate () e getdate () perché la differenza cambia a seconda di quale sia la data.

Anche l’integrazione CLR non è un’opzione per me.

La soluzione che avevo trovato per questo problema qualche mese prima era quella di avere una tabella di ora legale che memorizzava l’inizio e la fine dei giorni di risparmio di luce per i prossimi 100 anni, questa soluzione sembrava inelegante ma le conversioni erano veloci (semplice ricerca tabella)

Crea due tabelle e poi unisciti a loro per convertire le date GMT memorizzate in ora locale:

TimeZones eg --------- ---- TimeZoneId 19 Name Eastern (GMT -5) Offset -5 

Crea la tabella dell’ora legale e compilala con quante più informazioni possibili (le leggi locali cambiano continuamente, quindi non c’è modo di prevedere quale sarà il futuro dei dati in futuro)

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

Unisciti a loro in questo modo:

 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 

Convertire le date in questo modo:

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

Se sei negli Stati Uniti e sei interessato solo a passare da UTC / GMT a un fuso orario fisso (come EDT) questo codice dovrebbe essere sufficiente. Oggi l’ho montato e credo sia corretto, ma lo uso a proprio rischio.

Aggiunge una colonna calcasting a una tabella ‘myTable’ assumendo che le date siano nella colonna ‘date’. Spero che qualcun altro lo trovi utile.

 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) 

Una soluzione molto più semplice e generica che considera l’ora legale. Data una data UTC in “YourDateHere”:

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

FOR READ-ONLY Usa questo (ispirato alla soluzione errata di Bob Albright ):

 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 

Ho postato questa risposta dopo aver provato a modificare la risposta sbagliata di Bob Albright . Ho corretto i tempi e rimosso abs superfluo (), ma le mie modifiche sono state respinte più volte. Ho provato a spiegare, ma sono stato licenziato come un noob. Il suo è un ottimo approccio al problema! Mi ha fatto iniziare nella giusta direzione. Odio creare questa risposta separata quando ha solo bisogno di un piccolo aggiustamento, ma ho provato ¯ \ _ (ツ) _ / ¯

Se uno di questi problemi ti riguarda, non devi mai archiviare le ore locali nel database:

  1. Con DST è che c’è un “ora di incertezza” attorno al periodo di ricaduta in cui un orario locale non può essere convertito in modo univoco. Se sono richieste date e ore esatte, quindi memorizzare in UTC.
  2. Se si desidera mostrare agli utenti la data e l’ora nel proprio fuso orario, anziché il fuso orario in cui si è svolta l’azione, memorizzare in UTC.

Nella risposta di Eric Z Beard , il seguente 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 

potrebbe essere più precisamente:

 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 

(codice precedente non testato)

La ragione di ciò è che l'istruzione sql "between" è inclusiva. Sul back-end di DST, ciò comporterebbe un tempo di 2AM NON convertito in 1AM. Ovviamente la probabilità che il momento sia 2AM è piccola, ma può accadere e comporterebbe una conversione non valida.

Mantieni una tabella TimeZone o esegui il shell out con un proc memorizzato esteso (xp_cmdshell o un componente COM o il tuo) e chiedi al sistema operativo di farlo. Se segui la rotta xp, probabilmente vorrai memorizzare l’offset per un giorno.

Mi piace la risposta fornita da @Eric Z Beard .

Tuttavia, per evitare di eseguire un join ogni volta, che dire di questo?

 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 

Poi

  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) 

finalmente diventando

  dateadd(hh, @offset, TheDateToConvert) 

Ho letto molti post StackOverflow riguardo a questo problema e ho trovato molti metodi. Qualche “sorta di” ok. Ho anche trovato questo riferimento MS ( https://msdn.microsoft.com/en-us/library/mt612795.aspx ) che ho provato a utilizzare nel mio script. Sono riuscito a ottenere il risultato richiesto, ma non sono sicuro che questo verrà eseguito sulla versione 2005. Ad ogni modo, spero che questo aiuti.

FNC per restituire PST dal sistema UTC predefinito

 CREATE FUNCTION dbo.GetPst() RETURNS DATETIME AS BEGIN RETURN SYSDATETIMEOFFSET() AT TIME ZONE 'Pacific Standard Time' END SELECT dbo.GetPst() 

Fnc per restituire PST dal timestamp fornito

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

Sto usando questo perché tutte le mie date sono da ora in avanti.

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

Per le date storiche (o per gestire i futuri cambiamenti in DST, suppongo che la soluzione di Bob Albright sarebbe la strada da percorrere.

La modifica che apporto al mio codice consiste nell’utilizzare la colonna di destinazione:

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

Finora, sembra funzionare, ma sono felice di ricevere un feedback.

Ecco il codice che uso per creare il mio timezone table. È un po ‘ingenuo, ma di solito è abbastanza buono.

ipotesi:

  1. Presuppone le sole regole USA (DST è 2:00 su alcune domenica predefinite, ecc.).
  2. Presume che non hai date precedenti al 1970
  3. Presuppone che tu conosca le correzioni locali del fuso orario (es .: EST = -05: 00, EDT = -04: 00, ecc.)

Ecco l’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