Convertir la colonne Datetime de l'heure UTC en heure locale dans l'instruction select

Question

Je fais quelques requêtes de sélection SQL et j'aimerais convertir ma colonne de date / heure UTC en heure locale afin qu'elle soit affichée comme heure locale dans les résultats de ma requête. Notez que je ne cherche pas à convertir les données via le code, mais plutôt lorsque je fais des requêtes SQL manuelles et aléatoires sur mes bases de données.

Merci d'avance

Answer 1

Vous pouvez le faire comme suit sur SQL Server 2008 ou version ultérieure:

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

Vous pouvez aussi faire le moins bavard:

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

Quoi que vous fassiez, n'utilisez pas - pour soustraire des dates, car l'opération n'est pas atomique et vous obtiendrez parfois des résultats indéterminés en raison de conditions de concurrence entre la date / heure système et la date / heure locale vérifiées à des moments différents. (c'est-à-dire non atomiquement).

Answer 2

Si vous avez besoin d'une conversion autre que celle de votre serveur, voici une fonction qui vous permet de transmettre un décalage standard et des comptes pour l'heure avancée des États-Unis:

 -- =============================================
-- 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
 

Answer 3

J'ai trouvé une fonction d'extinction moyen d'être trop lent quand il y a beaucoup de données. Donc je l'ai fait à travers de se joindre à une table de fonction qui permettrait un calcul de l'heure diff. Il s'agit essentiellement de type datetime segments avec le décalage de l'heure. Une année serait de 4 lignes. Si la fonction de table

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

serait de retour ce tableau:

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 ne compte pour l'heure d'été. Un exemple de la façon dont il utilise est ci-dessous et l'article complet 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

Answer 4

Voici une version qui tient compte de l'heure avancée, du décalage UTC et qui n'est pas verrouillé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
 

Answer 5

En voici un plus simple qui prend dst en compte

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