Converter a coluna Datetime do UTC para a hora local na instrução select

Estou fazendo algumas consultas SQL select e gostaria de converter minha coluna datetime UTC em hora local para ser exibida como hora local nos resultados da minha consulta. Note, eu não estou olhando para fazer essa conversão via código, mas sim quando estou fazendo consultas SQL manuais e aleatórias contra meus bancos de dados.

Você pode fazer isso da seguinte maneira no SQL Server 2008 ou superior:

SELECT CONVERT(datetime, SWITCHOFFSET(CONVERT(datetimeoffset, MyTable.UtcColumn), DATENAME(TzOffset, SYSDATETIMEOFFSET()))) AS ColumnInLocalTime FROM MyTable 

Você também pode fazer o menos detalhado:

 SELECT DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), MyTable.UtcColumn) AS ColumnInLocalTime FROM MyTable 

Faça o que fizer, não use - para subtrair datas, porque a operação não é atômica e, ocasionalmente, obter resultados indeterminados devido a condições de corrida entre o datetime do sistema eo datetime local sendo verificado em momentos diferentes (ou seja, não atomicamente ).

Por favor, note que esta resposta não leva em conta o horário de verão. Se você quiser include um ajuste de horário de verão, veja também a seguinte pergunta:

Como criar a hora de verão e as funções Start e End no SQL Server

Não achei nenhum desses exemplos úteis para obter um datetime armazenado como UTC em um datetime em um fuso horário especificado (NÃO o fuso horário do servidor porque os bancos de dados SQL do Azure são executados como UTC). É assim que eu lidei com isso. Não é elegante, mas é simples e dá a resposta certa sem manter outras tabelas:

 select CONVERT(datetime, SWITCHOFFSET(dateTimeField, DATEPART(TZOFFSET, dateTimeField AT TIME ZONE 'Eastern Standard Time'))) 

Se você precisar de uma conversão diferente da localização do seu servidor, aqui está uma function que permite que você passe um deslocamento e contas padrão para o horário de verão dos EUA:

 -- ============================================= -- Author: Ron Smith -- Create date: 2013-10-23 -- Description: Converts UTC to DST -- based on passed Standard offset -- ============================================= CREATE FUNCTION [dbo].[fn_UTC_to_DST] ( @UTC datetime, @StandardOffset int ) RETURNS datetime AS BEGIN declare @DST datetime, @SSM datetime, -- Second Sunday in March @FSN datetime -- First Sunday in November -- get DST Range set @SSM = datename(year,@UTC) + '0314' set @SSM = dateadd(hour,2,dateadd(day,datepart(dw,@SSM)*-1+1,@SSM)) set @FSN = datename(year,@UTC) + '1107' set @FSN = dateadd(second,-1,dateadd(hour,2,dateadd(day,datepart(dw,@FSN)*-1+1,@FSN))) -- add an hour to @StandardOffset if @UTC is in DST range if @UTC between @SSM and @FSN set @StandardOffset = @StandardOffset + 1 -- convert to DST set @DST = dateadd(hour,@StandardOffset,@UTC) -- return converted datetime return @DST END GO 

Se a ativação do CLR em seu database for uma opção, bem como o uso do fuso horário do sql server, ele poderá ser escrito em .Net com bastante facilidade.

 public partial class UserDefinedFunctions { [Microsoft.SqlServer.Server.SqlFunction] public static SqlDateTime fn_GetLocalFromUTC(SqlDateTime UTC) { if (UTC.IsNull) return UTC; return new SqlDateTime(UTC.Value.ToLocalTime()); } } 

Um valor de data e hora UTC entra e o valor de data e hora local relativo ao servidor sai. Valores nulos retornam nulo.

Usando novas oportunidades do SQL Server 2016:

 CREATE FUNCTION ToLocalTime(@dtUtc datetime, @timezoneId nvarchar(256)) RETURNS datetime AS BEGIN return @dtUtc AT TIME ZONE 'UTC' AT TIME ZONE @timezoneId /* -- second way, faster return SWITCHOFFSET(@dtUtc , DATENAME(tz, @dtUtc AT TIME ZONE @timezoneId)) */ /* -- third way declare @dtLocal datetimeoffset set @dtLocal = @dtUtc AT TIME ZONE @timezoneId return dateadd(minute, DATEPART (TZoffset, @dtLocal), @dtUtc) */ END GO 

Mas o procedimento clr funciona 5 vezes mais rápido: ‘- (

Preste atenção que o deslocamento para um fuso horário pode mudar para o inverno ou horário de verão. Por exemplo

 select cast('2017-02-08 09:00:00.000' as datetime) AT TIME ZONE 'Eastern Standard Time' select cast('2017-08-08 09:00:00.000' as datetime) AT TIME ZONE 'Eastern Standard Time' 

resultados:

 2017-02-08 09:00:00.000 -05:00 2017-08-08 09:00:00.000 -04:00 

Você não pode simplesmente adicionar offset constante.

Aqui está uma versão que considera o horário de verão, o deslocamento UTC e não está bloqueada em um determinado ano.

 --------------------------------------------------------------------------------------------------- --Name: udfToLocalTime.sql --Purpose: To convert UTC to local US time accounting for DST --Author: Patrick Slesicki --Date: 3/25/2014 --Notes: Works on SQL Server 2008R2 and later, maybe SQL Server 2008 as well. -- Good only for US States observing the Energy Policy Act of 2005. -- Function doesn't apply for years prior to 2007. -- Function assumes that the 1st day of the week is Sunday. --Tests: -- SELECT dbo.udfToLocalTime('2014-03-09 9:00', DEFAULT) -- SELECT dbo.udfToLocalTime('2014-03-09 10:00', DEFAULT) -- SELECT dbo.udfToLocalTime('2014-11-02 8:00', DEFAULT) -- SELECT dbo.udfToLocalTime('2014-11-02 9:00', DEFAULT) --------------------------------------------------------------------------------------------------- ALTER FUNCTION udfToLocalTime ( @UtcDateTime AS DATETIME ,@UtcOffset AS INT = -8 --PST ) RETURNS DATETIME AS BEGIN DECLARE @PstDateTime AS DATETIME ,@Year AS CHAR(4) ,@DstStart AS DATETIME ,@DstEnd AS DATETIME ,@Mar1 AS DATETIME ,@Nov1 AS DATETIME ,@MarTime AS TIME ,@NovTime AS TIME ,@Mar1Day AS INT ,@Nov1Day AS INT ,@MarDiff AS INT ,@NovDiff AS INT SELECT @Year = YEAR(@UtcDateTime) ,@MarTime = CONVERT(TIME, DATEADD(HOUR, -@UtcOffset, '1900-01-01 02:00')) ,@NovTime = CONVERT(TIME, DATEADD(HOUR, -@UtcOffset - 1, '1900-01-01 02:00')) ,@Mar1 = CONVERT(CHAR(16), @Year + '-03-01 ' + CONVERT(CHAR(5), @MarTime), 126) ,@Nov1 = CONVERT(CHAR(16), @Year + '-11-01 ' + CONVERT(CHAR(5), @NovTime), 126) ,@Mar1Day = DATEPART(WEEKDAY, @Mar1) ,@Nov1Day = DATEPART(WEEKDAY, @Nov1) --Get number of days between Mar 1 and DST start date IF @Mar1Day = 1 SET @MarDiff = 7 ELSE SET @MarDiff = 15 - @Mar1Day --Get number of days between Nov 1 and DST end date IF @Nov1Day = 1 SET @NovDiff = 0 ELSE SET @NovDiff = 8 - @Nov1Day --Get DST start and end dates SELECT @DstStart = DATEADD(DAY, @MarDiff, @Mar1) ,@DstEnd = DATEADD(DAY, @NovDiff, @Nov1) --Change UTC offset if @UtcDateTime is in DST Range IF @UtcDateTime >= @DstStart AND @UtcDateTime < @DstEnd SET @UtcOffset = @UtcOffset + 1 --Get Conversion SET @PstDateTime = DATEADD(HOUR, @UtcOffset, @UtcDateTime) RETURN @PstDateTime END GO 

Eu achei a maneira de uma function ser muito lenta quando há muitos dados. Então eu fiz isso através da junit a uma function de tabela que permitiria um cálculo da diferença horária. É basicamente segmentos datetime com o deslocamento de hora. Um ano seria de 4 linhas. Então a function de mesa

 dbo.fn_getTimeZoneOffsets('3/1/2007 7:00am', '11/5/2007 9:00am', 'EPT') 

retornaria esta tabela:

 startTime endTime offset isHr2 3/1/07 7:00 3/11/07 6:59 -5 0 3/11/07 7:00 11/4/07 6:59 -4 0 11/4/07 7:00 11/4/07 7:59 -5 1 11/4/07 8:00 11/5/07 9:00 -5 0 

Ele conta para o horário de verão. Uma amostra de como ela é usada está abaixo e a postagem completa do blog está aqui .

 select mt.startTime as startUTC, dateadd(hh, tzStart.offset, mt.startTime) as startLocal, tzStart.isHr2 from MyTable mt inner join dbo.fn_getTimeZoneOffsets(@startViewUTC, @endViewUTC, @timeZone) tzStart on mt.startTime between tzStart.startTime and tzStart.endTime 

Não há uma maneira simples de fazer isso de maneira correta e genérica.

Primeiro de tudo, deve ser entendido que o deslocamento depende da data em questão, o fuso horário e horário de verão. GetDate()-GetUTCDate somente fornece a compensação hoje na TZ do servidor, o que não é relevante.

Eu vi apenas duas soluções de trabalho e eu pesquisei muito.

1) Uma function SQL personalizada com algumas tabelas de dados de base, como fuso horário e regras de horário de verão por TZ. Trabalhando mas não muito elegante. Não posso postar porque não possuo o código.

EDIT: aqui está um exemplo deste método https://gist.github.com/drumsta/16b79cee6bc195cd89c8

2) Adicione um assembly .net ao database. O Net pode fazer isso com muita facilidade. Isso está funcionando muito bem, mas a desvantagem é que você precisa configurar vários parâmetros no nível do servidor e a configuração é facilmente quebrada, por exemplo, se você restaurar o database. Eu uso esse método, mas eu não posso postá-lo desde que eu não possuo o código.

  declare @mydate2 datetime set @mydate2=Getdate() select @mydate2 as mydate, dateadd(minute, datediff(minute,getdate(),@mydate2),getutcdate()) 

A resposta de Ron contém um erro. Ele usa 2:00 AM, horário local, onde o equivalente UTC é necessário. Eu não tenho pontos de reputação suficientes para comentar sobre a resposta de Ron, então uma versão corrigida aparece abaixo:

 -- ============================================= -- Author: Ron Smith -- Create date: 2013-10-23 -- Description: Converts UTC to DST -- based on passed Standard offset -- ============================================= CREATE FUNCTION [dbo].[fn_UTC_to_DST] ( @UTC datetime, @StandardOffset int ) RETURNS datetime AS BEGIN declare @DST datetime, @SSM datetime, -- Second Sunday in March @FSN datetime -- First Sunday in November -- get DST Range set @SSM = datename(year,@UTC) + '0314' set @SSM = dateadd(hour,2 - @StandardOffset,dateadd(day,datepart(dw,@SSM)*-1+1,@SSM)) set @FSN = datename(year,@UTC) + '1107' set @FSN = dateadd(second,-1,dateadd(hour,2 - (@StandardOffset + 1),dateadd(day,datepart(dw,@FSN)*-1+1,@FSN))) -- add an hour to @StandardOffset if @UTC is in DST range if @UTC between @SSM and @FSN set @StandardOffset = @StandardOffset + 1 -- convert to DST set @DST = dateadd(hour,@StandardOffset,@UTC) -- return converted datetime return @DST END 

Nenhum destes trabalhou para mim, mas este abaixo funcionou 100%. Espero que isso possa ajudar os outros tentando convertê-lo como eu era.

 CREATE FUNCTION [dbo].[fn_UTC_to_EST] ( @UTC datetime, @StandardOffset int ) RETURNS datetime AS BEGIN declare @DST datetime, @SSM datetime, -- Second Sunday in March @FSN datetime -- First Sunday in November -- get DST Range set @SSM = DATEADD(dd,7 + (6-(DATEDIFF(dd,0,DATEADD(mm,(YEAR(GETDATE())-1900) * 12 + 2,0))%7)),DATEADD(mm,(YEAR(GETDATE())-1900) * 12 + 2,0))+'02:00:00' set @FSN = DATEADD(dd, (6-(DATEDIFF(dd,0,DATEADD(mm,(YEAR(GETDATE())-1900) * 12 + 10,0))%7)),DATEADD(mm,(YEAR(GETDATE())-1900) * 12 + 10,0)) +'02:00:00' -- add an hour to @StandardOffset if @UTC is in DST range if @UTC between @SSM and @FSN set @StandardOffset = @StandardOffset + 1 -- convert to DST set @DST = dateadd(hour,@StandardOffset,@UTC) -- return converted datetime return @DST END 

O timestamp do UNIX é meramente o número de segundos entre uma data específica e a Unix Epoch,

SELECT DATEDIFF (SECOND, {d ‘1970-01-01’}, GETDATE ()) // Isso retornará o timestamp do UNIX no SQL Server

você pode criar uma function para a data e hora local para a conversão UTC do Unix usando a Função de Deslocamento do País para o carimbo de data / hora do Unix no servidor SQL

Como um aviso – se você for usar o seguinte (observe os milissegundos em vez de minutos):

  SELECT DATEADD(ms, DATEDIFF(ms, GETUTCDATE(), GETDATE()), MyTable.UtcColumn) AS ColumnInLocalTime FROM MyTable 

Tenha em mente que a parte DATEDIFF nem sempre retornará o mesmo número. Portanto, não o use para comparar os DateTimes a milissegundos.

Descobri que essa function é mais rápida que outras soluções usando uma tabela ou loops separados. É apenas uma afirmação básica do caso. Dado que todos os meses entre abril e outubro têm um deslocamento de -4 horas (Hora do Leste), só precisamos adicionar mais algumas linhas de casos para os dias de margem. Caso contrário, o deslocamento é de -5 horas.

Isso é específico para uma conversão do UTC para o horário do leste, mas funções adicionais de fuso horário podem ser adicionadas conforme necessário.

 USE [YourDatabaseName] GO /****** Object: UserDefinedFunction [dbo].[ConvertUTCtoEastern] Script Date: 11/2/2016 5:21:52 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE FUNCTION [dbo].[ConvertUTCtoEastern] ( @dtStartDate DATETIME ) RETURNS DATETIME AS BEGIN DECLARE @Working DATETIME DECLARE @Returned DATETIME SET @Working = @dtStartDate SET @Working = case when month(@Working) between 4 and 10 then dateadd(HH,-4,@Working) when @Working between '2017-03-12' and '2017-11-05' then dateadd(HH,-4,@Working) when @Working between '2016-03-13' and '2016-11-06' then dateadd(HH,-4,@Working) when @Working between '2015-03-08' and '2015-11-01' then dateadd(HH,-4,@Working) when @Working between '2014-03-09' and '2014-11-02' then dateadd(HH,-4,@Working) when @Working between '2013-03-10' and '2013-11-03' then dateadd(HH,-4,@Working) when @Working between '2012-03-11' and '2012-11-04' then dateadd(HH,-4,@Working) else dateadd(HH,-5,@Working) end SET @Returned = @Working RETURN @Returned END GO 

Isso deve ser capaz de obter o horário do servidor com o DST

 declare @dt datetime set @dt = getutcdate() -- GMT equivalent 

O sysdatetimeoffset leva o DST em conta

 select [InputTime] = @dt , [LocalTime2] = dateadd(mi, datediff(mi, sysdatetimeoffset(),getdate()), @dt) 

Primeira function: configurada para fuso horário italiano (+1, +2), datas de comutação: último domingo de março e outubro, retorna a diferença entre o fuso horário atual e o datetime como parâmetro.

 Returns: current timezone < parameter timezone ==> +1 current timezone > parameter timezone ==> -1 else 0 

O código é:

 CREATE FUNCTION [dbo].[UF_ADJUST_OFFSET] ( @dt_utc datetime2(7) ) RETURNS INT AS BEGIN declare @month int, @year int, @current_offset int, @offset_since int, @offset int, @yearmonth varchar(8), @changeoffsetdate datetime2(7) declare @lastweek table(giorno datetime2(7)) select @current_offset = DATEDIFF(hh, GETUTCDATE(), GETDATE()) select @month = datepart(month, @dt_utc) if @month < 3 or @month > 10 Begin Set @offset_since = 1 Goto JMP End if @month > 3 and @month < 10 Begin Set @offset_since = 2 Goto JMP End --If i'm here is march or october select @year = datepart(yyyy, @dt_utc) if @month = 3 Begin Set @yearmonth = cast(@year as varchar) + '-03-' Insert Into @lastweek Values(@yearmonth + '31 03:00:00.000000'),(@yearmonth + '30 03:00:00.000000'),(@yearmonth + '29 03:00:00.000000'),(@yearmonth + '28 03:00:00.000000'), (@yearmonth + '27 03:00:00.000000'),(@yearmonth + '26 03:00:00.000000'),(@yearmonth + '25 03:00:00.000000') --Last week of march Select @changeoffsetdate = giorno From @lastweek Where datepart(weekday, giorno) = 1 if @dt_utc < @changeoffsetdate Begin Set @offset_since = 1 End Else Begin Set @offset_since = 2 End End if @month = 10 Begin Set @yearmonth = cast(@year as varchar) + '-10-' Insert Into @lastweek Values(@yearmonth + '31 03:00:00.000000'),(@yearmonth + '30 03:00:00.000000'),(@yearmonth + '29 03:00:00.000000'),(@yearmonth + '28 03:00:00.000000'), (@yearmonth + '27 03:00:00.000000'),(@yearmonth + '26 03:00:00.000000'),(@yearmonth + '25 03:00:00.000000') --Last week of october Select @changeoffsetdate = giorno From @lastweek Where datepart(weekday, giorno) = 1 if @dt_utc > @changeoffsetdate Begin Set @offset_since = 1 End Else Begin Set @offset_since = 2 End End JMP: if @current_offset < @offset_since Begin Set @offset = 1 End Else if @current_offset > @offset_since Set @offset = -1 Else Set @offset = 0 Return @offset END 

Então a function que converte data

 CREATE FUNCTION [dbo].[UF_CONVERT] ( @dt_utc datetime2(7) ) RETURNS datetime AS BEGIN declare @offset int Select @offset = dbo.UF_ADJUST_OFFSET(@dt_utc) if @dt_utc >= '9999-12-31 22:59:59.9999999' set @dt_utc = '9999-12-31 23:59:59.9999999' Else set @dt_utc = (SELECT DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), @dt_utc) ) if @offset <> 0 Set @dt_utc = dateadd(hh, @offset, @dt_utc) RETURN @dt_utc END 

Aqui está um mais simples que leva dst em conta

 CREATE FUNCTION [dbo].[UtcToLocal] ( @p_utcDatetime DATETIME ) RETURNS DATETIME AS BEGIN RETURN DATEADD(MINUTE, DATEDIFF(MINUTE, GETUTCDATE(), @p_utcDatetime), GETDATE()) END