sql server - microsoft - Conversión efectiva de fechas entre UTC y local(es decir, hora de PST) en SQL 2005

sql server descargar (11)

¿Cuál es la mejor manera de convertir una fecha y hora UTC en hora local? No es tan simple como las diferencias getutcdate () y getdate () porque la diferencia cambia según la fecha.

La integración de CLR tampoco es una opción para mí.

La solución que había surgido para este problema hace unos meses era tener una tabla de ahorro de luz diurna que almacenara los días de inicio y finalización del horario de verano durante los siguientes 100 años aproximadamente, esta solución parecía poco elegante pero las conversiones fueron rápidas (simple búsqueda de tabla)

Aquí está el código que uso para hacer mi tabla de zona horaria. Es un poco ingenuo, pero por lo general es lo suficientemente bueno.

Suposiciones

Asume que solo las reglas de EE. UU. (DST es 2AM en un domingo predefinido, etc.).
Asume que no tienes fechas anteriores a 1970
Asume que conoce los desplazamientos locales de la zona horaria (es decir: EST = -05: 00, EDT = -04: 00, etc.)

Aquí está el 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

Cree dos tablas y luego únase a ellas para convertir las fechas GMT almacenadas a la hora local:

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

Cree la tabla de ahorro de luz diurna y llénela con tanta información como pueda (las leyes locales cambian todo el tiempo, por lo que no hay forma de predecir cómo serán los datos en el futuro)

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

Únete a ellos así:

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

Convierte fechas como esta:

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

En la respuesta de Eric Z Beard , el siguiente 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

podría ser más exactamente:

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

(Código anterior no probado)

La razón para esto es que la declaración sql "between" es inclusiva. En el back-end de DST, esto daría como resultado un tiempo de 2AM que NO se convertiría a 1AM. Por supuesto, la probabilidad de que el tiempo sea de las 2 AM, precisamente, es pequeña, pero puede suceder y daría como resultado una conversión no válida.

Estoy usando esto porque todas mis fechas son de ahora en adelante.

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

Para fechas históricas (o para manejar futuros cambios en el horario de verano, supongo que la solución de Bob Albright sería el camino a seguir.

La modificación que realizo en mi código es usar la columna de destino:

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

Hasta ahora, esto parece funcionar, pero me complace recibir comentarios.

He leído muchas publicaciones de con respecto a este tema y he encontrado muchos métodos. Algún "tipo de" ok. También encontré esta referencia de MS ( https://msdn.microsoft.com/en-us/library/mt612795.aspx ) que traté de utilizar en mi script. Logré lograr el resultado requerido PERO no estoy seguro si esto funcionará en la versión 2005. De cualquier manera, espero que esto ayude.

Fnc para devolver PST desde el sistema UTC por defecto

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

Fnc para devolver PST desde la marca de tiempo proporcionada

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

Mantenga una tabla TimeZone o desembolse un proceso almacenado extendido (xp_cmdshell o un componente COM, o el suyo propio) y solicite al sistema operativo que lo haga. Si vas a la ruta xp, probablemente quieras almacenar en caché el desplazamiento por un día.

Me gusta la respuesta @Eric Z Beard siempre.

Sin embargo, para evitar realizar una unión cada vez, ¿qué tal esto?

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

Entonces

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 convirtiéndose

dateadd(hh, @offset, TheDateToConvert)

Si alguno de estos problemas le afecta, nunca debe almacenar horas locales en la base de datos:

Con DST es que hay una "hora de incertidumbre" en el período de retroceso en el que una hora local no puede convertirse sin ambigüedad. Si se requieren fechas y horas exactas, entonces almacene en UTC.
Si desea mostrar a los usuarios la fecha y la hora en su propia zona horaria, en lugar de la zona horaria en la que tuvo lugar la acción, almacénela en UTC.

Si se encuentra en los EE. UU. Y solo está interesado en pasar de UTC / GMT a un huso horario fijo (como EDT), este código debería ser suficiente. Lo preparé hoy y creo que es correcto, pero utilícelo bajo su propio riesgo.

Agrega una columna calculada a una tabla ''myTable'' suponiendo que sus fechas están en la columna ''fecha''. Espero que alguien más encuentre esto útil.

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 solución mucho más simple y genérica que considera el ahorro de luz natural. Dada una fecha UTC en "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]

PARA LECTURA ÚNICA Use esto (inspirado en la solución incorrecta de 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

Publiqué esta respuesta después de que traté de editar la respuesta incorrecta de Bob Albright . Corregí los tiempos y eliminé abs superfluos (), pero mis ediciones fueron rechazadas varias veces. Intenté explicarlo, pero me despidieron como novato. ¡Es un GRAN enfoque al problema! Me hizo comenzar en la dirección correcta. Odio crear esta respuesta por separado cuando solo necesita un pequeño ajuste, pero probé ¯ / _ (ツ) _ / ¯