Convertir la colonne Datetime de UTC en heure locale dans la déclaration select
je fais quelques requêtes SQL select et je voudrais convertir ma colonne UTC datetime en heure locale pour être affiché en heure locale dans mes résultats de requête. Note, Je ne cherche pas à faire cette conversion via le code mais plutôt quand je fais des requêtes SQL manuelles et aléatoires contre mes bases de données.
18 réponses
vous pouvez le faire comme suit sur SQL Server 2008 ou plus:
SELECT CONVERT(datetime,
SWITCHOFFSET(CONVERT(datetimeoffset,
MyTable.UtcColumn),
DATENAME(TzOffset, SYSDATETIMEOFFSET())))
AS ColumnInLocalTime
FROM MyTable
, Vous pouvez aussi faire le moins verbeux:
SELECT DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), MyTable.UtcColumn)
AS ColumnInLocalTime
FROM MyTable
quoi que vous fassiez, do not utilisez -
pour soustraire les dates, parce que l'opération n'est pas atomique, et vous obtiendrez à l'occasion des résultats indéterminés en raison des conditions de course entre le système datetime et la date locale datetime étant vérifiée à des moments différents (i.e., non atomiquement).
veuillez noter que cette réponse ne tient pas compte de DST. Si vous voulez inclure un ajustement de L'heure D'été, s'il vous plaît voir aussi la question SO suivante:
comment créer la fonction de début et de fin de L'heure D'été dans le serveur SQL
Je n'ai trouvé aucun de ces exemples utiles pour obtenir un datetime stocké en UTC vers un datetime dans un fuseau horaire spécifié (pas le fuseau horaire du serveur car les bases de données SQL Azure tournent en UTC). C'est de cette façon que j'ai manipulé. Ce n'est pas Élégant, mais c'est simple et vous donne la bonne réponse sans maintenir d'autres tables:
select CONVERT(datetime, SWITCHOFFSET(dateTimeField, DATEPART(TZOFFSET,
dateTimeField AT TIME ZONE 'Eastern Standard Time')))
si vous avez besoin d'une conversion autre que l'emplacement de votre serveur, voici une fonction qui vous permet de passer un décalage standard et des comptes pour US Daylight Savings Time:
-- =============================================
-- 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
si activer CLR sur votre base de données est une option en plus d'utiliser le fuseau horaire du serveur sql, il peut être écrit en .Net assez facilement.
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());
}
}
une valeur de datetime UTC entre et la valeur de datetime locale relative au serveur sort. Les valeurs nulles renvoient null.
utilisant les nouvelles possibilités du serveur SQL 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
mais la procédure clr fonctionne 5 fois plus vite: '- (
attention qui compensent pour un fuseau horaire peut changer à l'heure d'hiver ou d'été. Par exemple
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'
résultats:
2017-02-08 09:00:00.000 -05:00
2017-08-08 09:00:00.000 -04:00
vous ne pouvez pas juste ajouter l'offset constant.
Voici une version qui tient compte de l'heure d'été, de la compensation UTC et qui n'est pas immobilisée pour une année donnée.
---------------------------------------------------------------------------------------------------
--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
j'ai trouvé que la fonction one off est trop lente quand il y a beaucoup de données. Donc je l'ai fait en me joignant à une fonction de table qui permettrait un calcul de la différence d'heure. Il s'agit essentiellement de segments datetime avec décalage horaire. Une année serait de 4 lignes. Ainsi la fonction de table
dbo.fn_getTimeZoneOffsets('3/1/2007 7:00am', '11/5/2007 9:00am', 'EPT')
retournerait cette table:
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
il tient compte de l'heure d'été. Un exemple de comment il est utilise est ci-dessous et le blog complet post est ici .
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
il n'y a pas de moyen simple de le faire de manière correcte et générique.
tout d'abord, il faut comprendre que le décalage dépend de la date en question, le fuseau horaire et DST.
GetDate()-GetUTCDate
ne vous donne l'offset qu'aujourd'hui à la TZ du serveur, ce qui n'est pas pertinent.
Je n'ai vu que deux solutions de travail et j'ai beaucoup de recherche.
1) une fonction SQL personnalisée avec un couple de tables de données de base telles que le temps Des Zones et des règles d'heure d'été par TZ. De travail, mais pas très élégant. Je ne peux pas le poster car je ne possède pas le code.
EDIT: voici un exemple de cette méthode https://gist.github.com/drumsta/16b79cee6bc195cd89c8
2) Ajouter un assemblage .net à la base de données, .Net peut le faire très facilement. Cela fonctionne très bien, mais l'inconvénient est que vous avez besoin de configurer plusieurs paramètres au niveau du serveur et la configuration est facilement restaurer la base de données. J'utilise cette méthode mais je ne peux pas la poster car je ne possède pas le code.
declare @mydate2 datetime
set @mydate2=Getdate()
select @mydate2 as mydate,
dateadd(minute, datediff(minute,getdate(),@mydate2),getutcdate())
la réponse de Ron contient une erreur. Il est utilisé à 2h00 du matin, heure locale, lorsque l'équivalent en TUC est requis. Je n'ai pas assez de points de réputation pour commenter la réponse de Ron donc une version corrigée apparaît ci-dessous:
-- =============================================
-- 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
aucun de ceux-ci n'a fonctionné pour moi, mais cela ci-dessous a fonctionné à 100%. J'espère que ça aidera les autres à le convertir comme je l'ai été.
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
le timestamp UNIX est simplement le nombre de secondes entre une date particulière et L'époque Unix,
SELECT DATEDIFF (SECOND, {d '1970-01'}, GETDATE ()) / / This Will Return the UNIX timestamp In SQL server
vous pouvez créer une fonction pour la date heure locale pour la conversion Unix UTC en utilisant L'Offset du pays Fonction Pour Unix Time Stamp dans le serveur SQL
comme avertissement - si vous allez utiliser ce qui suit (notez les millisecondes au lieu de minutes):
SELECT DATEADD(ms, DATEDIFF(ms, GETUTCDATE(), GETDATE()), MyTable.UtcColumn)
AS ColumnInLocalTime
FROM MyTable
gardez à l'esprit que la partie DATEDIFF ne retournera pas toujours le même numéro. Donc, ne l'utilisez pas pour comparer les temps de données vers le bas à des millisecondes.
j'ai trouvé que cette fonction est plus rapide que d'autres solutions en utilisant une table séparée ou des boucles. C'est juste un exposé de cas. Étant donné que tous les mois entre avril et octobre ont un décalage de-4 heures (Heure de l'est) nous avons juste besoin d'ajouter quelques lignes de cas supplémentaires pour les jours marginaux. Sinon, le décalage est de 5 heures.
il s'agit d'une conversion spécifique de L'heure UTC à l'Heure de l'est, mais des fonctions de fuseau horaire supplémentaires peuvent être ajoutées au besoin.
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
cela devrait être en mesure d'obtenir le temps de serveur avec DST
declare @dt datetime
set @dt = getutcdate() -- GMT equivalent
sysdatetimeoffset prend en compte de l'heure d'été
select [InputTime] = @dt
, [LocalTime2] = dateadd(mi, datediff(mi, sysdatetimeoffset(),getdate()), @dt)
première fonction: configuré pour le fuseau horaire italien (+1, +2), Changer les dates: dernier dimanche de mars et octobre, retourner la différence entre le fuseau horaire actuel et le datetime comme paramètre.
Returns:
current timezone < parameter timezone ==> +1
current timezone > parameter timezone ==> -1
else 0
le code est:
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
puis la fonction qui convertit date
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
-- obtenir l'heure normale de l'Inde de utc
créer la fonction dbo.getISTTime ( @UTCDate datetime ) Retourne datetime COMME BEGIN
RETURN dateadd(minute,330,@UTCDate)
fin GO
En voici un plus simple qui tient compte de l'heure d'été""
CREATE FUNCTION [dbo].[UtcToLocal]
(
@p_utcDatetime DATETIME
)
RETURNS DATETIME
AS
BEGIN
RETURN DATEADD(MINUTE, DATEDIFF(MINUTE, GETUTCDATE(), @p_utcDatetime), GETDATE())
END