wake-up-neo.net

Come posso ottenere l'offset corretto tra l'ora UTC e l'ora locale per una data precedente o successiva all'ora legale?

Attualmente uso quanto segue per ottenere un datetime locale da un datetime UTC:

SET @offset = DateDiff(minute, GetUTCDate(), GetDate())
SET @localDateTime = DateAdd(minute, @offset, @utcDateTime)

Il mio problema è che se l'ora legale si verifica tra GetUTCDate() e @utcDateTime, @localDateTime Finisce con un'ora di pausa.

C'è un modo semplice per convertire da UTC all'ora locale per una data che non è la data corrente?

Sto usando SQL Server 2005

30
Rachel

Il modo migliore per convertire una data UTC non corrente in ora locale è utilizzare il CLR. Il codice stesso è semplice; la parte difficile di solito è convincere la gente che il CLR non è puro male o paura ...

Per uno dei tanti esempi, dai un'occhiata a post sul blog di Harsh Chawla sull'argomento .

Sfortunatamente, non esiste nulla di integrato che possa gestire questo tipo di conversione, ad eccezione delle soluzioni basate su CLR. Potresti scrivere una funzione T-SQL che fa qualcosa del genere, ma poi dovresti implementare tu stesso la logica di cambio data e la definirei decisamente non facile.

19
Kevin Feasel

Ho sviluppato e pubblicato il progetto T-SQL Toolbox su codeplex per aiutare chiunque abbia difficoltà a gestire il datetime e il fuso orario in Microsoft SQL Server. È open source e completamente gratuito da usare.

Offre UDF di conversione datetime semplici utilizzando T-SQL semplice (senza CLR) oltre a tabelle di configurazione precompilate pronte all'uso. E ha il pieno supporto DST (ora legale).

Un elenco di tutti i fusi orari supportati è disponibile nella tabella "DateTimeUtil.Timezone" (fornita nel database T-SQL Toolbox).

Nel tuo esempio, puoi usare il seguente esempio:

SELECT [DateTimeUtil].[UDF_ConvertUtcToLocalByTimezoneIdentifier] (
    'W. Europe Standard Time', -- the target local timezone
    '2014-03-30 00:55:00' -- the original UTC datetime you want to convert
)

Ciò restituirà il valore datetime locale convertito.

Sfortunatamente, è supportato per SQL Server 2008 o versioni successive solo a causa di tipi di dati più recenti (DATA, ORA, DATA DATA2). Tuttavia, poiché viene fornito il codice sorgente completo, è possibile regolare facilmente le tabelle e gli UDF sostituendoli con DATETIME. Non ho un MSSQL 2005 disponibile per i test, ma dovrebbe funzionare anche con MSSQL 2005, quindi. In caso di domande, fammelo sapere.

15
adss

Uso sempre questo comando TSQL.

-- the utc value 
declare @utc datetime = '20/11/2014 05:14'

-- the local time

select DATEADD(hh, DATEDIFF(hh, getutcdate(), getdate()), @utc)

È molto semplice e fa il lavoro.

13
Ludo Bernaerts

Ho trovato questa risposta su StackOverflow che fornisce una funzione definita dall'utente che sembra tradurre con precisione i tempi dei dati

L'unica cosa che devi modificare è il @offset variabile nella parte superiore per impostarla sull'offset del fuso orario del server SQL che esegue questa funzione. Nel mio caso, il nostro server SQL utilizza EST, che è GMT - 5

Non è perfetto e probabilmente non funzionerà in molti casi, ad esempio ha offset TZ di mezz'ora o 15 minuti (per quelli che consiglierei una funzione CLR come Kevin consigliato ), tuttavia funziona abbastanza bene per la maggior parte dei fusi orari generici in Nord America.

CREATE FUNCTION [dbo].[UDTToLocalTime](@UDT AS DATETIME)  
RETURNS DATETIME
AS
BEGIN 
--====================================================
--Set the Timezone Offset (NOT During DST [Daylight Saving Time])
--====================================================
DECLARE @Offset AS SMALLINT
SET @Offset = -5

--====================================================
--Figure out the Offset Datetime
--====================================================
DECLARE @LocalDate AS DATETIME
SET @LocalDate = DATEADD(hh, @Offset, @UDT)

--====================================================
--Figure out the DST Offset for the UDT Datetime
--====================================================
DECLARE @DaylightSavingOffset AS SMALLINT
DECLARE @Year as SMALLINT
DECLARE @DSTStartDate AS DATETIME
DECLARE @DSTEndDate AS DATETIME
--Get Year
SET @Year = YEAR(@LocalDate)

--Get First Possible DST StartDay
IF (@Year > 2006) SET @DSTStartDate = CAST(@Year AS CHAR(4)) + '-03-08 02:00:00'
ELSE              SET @DSTStartDate = CAST(@Year AS CHAR(4)) + '-04-01 02:00:00'
--Get DST StartDate 
WHILE (DATENAME(dw, @DSTStartDate) <> 'sunday') SET @DSTStartDate = DATEADD(day, 1,@DSTStartDate)


--Get First Possible DST EndDate
IF (@Year > 2006) SET @DSTEndDate = CAST(@Year AS CHAR(4)) + '-11-01 02:00:00'
ELSE              SET @DSTEndDate = CAST(@Year AS CHAR(4)) + '-10-25 02:00:00'
--Get DST EndDate 
WHILE (DATENAME(dw, @DSTEndDate) <> 'sunday') SET @DSTEndDate = DATEADD(day,1,@DSTEndDate)

--Get DaylightSavingOffset
SET @DaylightSavingOffset = CASE WHEN @LocalDate BETWEEN @DSTStartDate AND @DSTEndDate THEN 1 ELSE 0 END

--====================================================
--Finally add the DST Offset 
--====================================================
RETURN DATEADD(hh, @DaylightSavingOffset, @LocalDate)
END



GO
11
Rachel

Per SQL Server 2016+, è possibile utilizzare AT TIME ZONE . gestirà automaticamente i tempi di risparmio della luce del giorno.

5
gotqn

Ci sono un paio di buone risposte a un domanda simile chiesto su Stack Overflow. Ho finito usando un approccio T-SQL dal seconda risposta di Bob Albright per ripulire un casino causato da un consulente di conversione dei dati.

Ha funzionato per quasi tutti i nostri dati, ma in seguito mi sono reso conto che il suo algoritmo funziona solo per date risalenti al 5 aprile 1987 , e noi aveva alcune date degli anni '40 che ancora non si convertivano correttamente. Alla fine avevamo bisogno delle date UTC nel nostro database di SQL Server per allinearle con un algoritmo in un programma di terze parti che utilizzava l'API Java per convertire da UTC all'ora locale .

Mi piace l'esempio CLRnella risposta di Kevin Feasel sopra usando l'esempio di Harsh Chawla, e mi piacerebbe anche confrontarlo con una soluzione che utilizza Java, poiché il nostro front-end usa Java per eseguire la conversione da UTC a ora locale.

Wikipedia menziona 8 diversi emendamenti costituzionali che comportano aggiustamenti del fuso orario prima del 1987, e molti di questi sono molto localizzati in stati diversi, quindi c'è una possibilità che CLR e Java possano interpretarli in modo diverso. Il codice dell'applicazione front-end utilizza dotnet o Java o le date precedenti al 1987 sono un problema per te?

3
kkarns
DECLARE @TimeZone VARCHAR(50)
EXEC MASTER.dbo.xp_regread 'HKEY_LOCAL_MACHINE', 'SYSTEM\CurrentControlSet\Control\TimeZoneInformation', 'TimeZoneKeyName', @TimeZone OUT
SELECT @TimeZone
DECLARE @someUtcTime DATETIME
SET @someUtcTime = '2017-03-05 15:15:15'
DECLARE @TimeBiasAtSomeUtcTime INT
SELECT @TimeBiasAtSomeUtcTime = DATEDIFF(MINUTE, @someUtcTime, @someUtcTime AT TIME ZONE @TimeZone)
SELECT DATEADD(MINUTE, @TimeBiasAtSomeUtcTime * -1, @someUtcTime)
2
Joost Versteegen

Puoi farlo facilmente con una stored procedure CLR.

[SqlFunction]
public static SqlDateTime ToLocalTime(SqlDateTime UtcTime, SqlString TimeZoneId)
{
    if (UtcTime.IsNull)
        return UtcTime;

    var timeZone = TimeZoneInfo.FindSystemTimeZoneById(TimeZoneId.Value);
    var localTime = TimeZoneInfo.ConvertTimeFromUtc(UtcTime.Value, timeZone);
    return new SqlDateTime(localTime);
}

È possibile memorizzare i fusi orari disponibili in una tabella:

CREATE TABLE TimeZones
(
    TimeZoneId NVARCHAR(32) NOT NULL CONSTRAINT PK_TimeZones PRIMARY KEY,
    DisplayName NVARCHAR(64) NOT NULL,
    SupportsDaylightSavingTime BIT NOT NULL,
)

E questa procedura memorizzata riempirà la tabella con i possibili fusi orari sul tuo server.

public partial class StoredProcedures
{
    [SqlProcedure]
    public static void PopulateTimezones()
    {
        using (var sql = new SqlConnection("Context Connection=True"))
        {
            sql.Open();

            using (var cmd = sql.CreateCommand())
            {
                cmd.CommandText = "DELETE FROM TimeZones";
                cmd.ExecuteNonQuery();

                cmd.CommandText = "INSERT INTO [dbo].[TimeZones]([TimeZoneId], [DisplayName], [SupportsDaylightSavingTime]) VALUES(@TimeZoneId, @DisplayName, @SupportsDaylightSavingTime);";
                var Id = cmd.Parameters.Add("@TimeZoneId", SqlDbType.NVarChar);
                var DisplayName = cmd.Parameters.Add("@DisplayName", SqlDbType.NVarChar);
                var SupportsDaylightSavingTime = cmd.Parameters.Add("@SupportsDaylightSavingTime", SqlDbType.Bit);

                foreach (var zone in TimeZoneInfo.GetSystemTimeZones())
                {
                    Id.Value = zone.Id;
                    DisplayName.Value = zone.DisplayName;
                    SupportsDaylightSavingTime.Value = zone.SupportsDaylightSavingTime;

                    cmd.ExecuteNonQuery();
                }
            }
        }
    }
}
2
Tim Cooke

Ecco una risposta scritta per una specifica applicazione del Regno Unito e basata esclusivamente su SELECT.

  1. Nessun offset del fuso orario (ad es. Regno Unito)
  2. Scritto per l'ora legale a partire dall'ultima domenica di marzo e terminando l'ultima domenica di ottobre (regole del Regno Unito)
  3. Non applicabile tra la mezzanotte e l'01 del giorno in cui inizia l'ora legale. Questo potrebbe essere corretto ma l'applicazione per cui è stato scritto non lo richiede.

    -- A variable holding an example UTC datetime in the UK, try some different values:
    DECLARE
    @App_Date datetime;
    set @App_Date = '20250704 09:00:00'
    
    -- Outputting the local datetime in the UK, allowing for daylight saving:
    SELECT
    case
    when @App_Date >= dateadd(day, 1 - datepart(weekday, dateadd(day, -1, dateadd(month, 3, dateadd(year, datediff(year, 0, @App_Date), 0)))), dateadd(day, -1, dateadd(month, 3, dateadd(year, datediff(year, 0, @App_Date), 0))))
        and @App_Date < dateadd(day, 1 - datepart(weekday, dateadd(day, -1, dateadd(month, 10, dateadd(year, datediff(year, 0, @App_Date), 0)))), dateadd(day, -1, dateadd(month, 10, dateadd(year, datediff(year, 0, @App_Date), 0))))
        then DATEADD(hour, 1, @App_Date) 
    else @App_Date 
    end
    
2
colinp_1

SQL Server versione 2016 risolverà questo problema na volta per tutte . Per le versioni precedenti una soluzione CLR è probabilmente la più semplice. O per una specifica ora legale (solo negli Stati Uniti), una funzione T-SQL può essere relativamente semplice.

Tuttavia, penso che potrebbe essere possibile una soluzione T-SQL generica. Fintanto che xp_regread funziona, prova questo:

CREATE TABLE #tztable (Value varchar(50), Data binary(56));
DECLARE @tzname varchar(150) = 'SYSTEM\CurrentControlSet\Control\TimeZoneInformation'
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE', @tzname, 'TimeZoneKeyName', @tzname OUT;
SELECT @tzname = 'SOFTWARE\Microsoft\Windows NT\CurrentVersion\Time Zones\' + @tzname
INSERT INTO #tztable
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE', @tzname, 'TZI';
SELECT                                                                                  -- See http://msdn.Microsoft.com/ms725481
 CAST(CAST(REVERSE(SUBSTRING(Data,  1, 4)) AS binary(4))      AS int) AS BiasMinutes,   -- UTC = local + bias: > 0 in US, < 0 in Europe!
 CAST(CAST(REVERSE(SUBSTRING(Data,  5, 4)) AS binary(4))      AS int) AS ExtraBias_Std, --   0 for most timezones
 CAST(CAST(REVERSE(SUBSTRING(Data,  9, 4)) AS binary(4))      AS int) AS ExtraBias_DST, -- -60 for most timezones: DST makes UTC 1 hour earlier
 -- When DST ends:
 CAST(CAST(REVERSE(SUBSTRING(Data, 13, 2)) AS binary(2)) AS smallint) AS StdYear,       -- 0 = yearly (else once)
 CAST(CAST(REVERSE(SUBSTRING(Data, 15, 2)) AS binary(2)) AS smallint) AS StdMonth,      -- 0 = no DST
 CAST(CAST(REVERSE(SUBSTRING(Data, 17, 2)) AS binary(2)) AS smallint) AS StdDayOfWeek,  -- 0 = Sunday to 6 = Saturday
 CAST(CAST(REVERSE(SUBSTRING(Data, 19, 2)) AS binary(2)) AS smallint) AS StdWeek,       -- 1 to 4, or 5 = last <DayOfWeek> of <Month>
 CAST(CAST(REVERSE(SUBSTRING(Data, 21, 2)) AS binary(2)) AS smallint) AS StdHour,       -- Local time
 CAST(CAST(REVERSE(SUBSTRING(Data, 23, 2)) AS binary(2)) AS smallint) AS StdMinute,
 CAST(CAST(REVERSE(SUBSTRING(Data, 25, 2)) AS binary(2)) AS smallint) AS StdSecond,
 CAST(CAST(REVERSE(SUBSTRING(Data, 27, 2)) AS binary(2)) AS smallint) AS StdMillisec,
 -- When DST starts:
 CAST(CAST(REVERSE(SUBSTRING(Data, 29, 2)) AS binary(2)) AS smallint) AS DSTYear,       -- See above
 CAST(CAST(REVERSE(SUBSTRING(Data, 31, 2)) AS binary(2)) AS smallint) AS DSTMonth,
 CAST(CAST(REVERSE(SUBSTRING(Data, 33, 2)) AS binary(2)) AS smallint) AS DSTDayOfWeek,
 CAST(CAST(REVERSE(SUBSTRING(Data, 35, 2)) AS binary(2)) AS smallint) AS DSTWeek,
 CAST(CAST(REVERSE(SUBSTRING(Data, 37, 2)) AS binary(2)) AS smallint) AS DSTHour,
 CAST(CAST(REVERSE(SUBSTRING(Data, 39, 2)) AS binary(2)) AS smallint) AS DSTMinute,
 CAST(CAST(REVERSE(SUBSTRING(Data, 41, 2)) AS binary(2)) AS smallint) AS DSTSecond,
 CAST(CAST(REVERSE(SUBSTRING(Data, 43, 2)) AS binary(2)) AS smallint) AS DSTMillisec
FROM #tztable;
DROP TABLE #tztable

Una funzione (complessa) T-SQL potrebbe utilizzare questi dati per determinare l'offset esatto per tutte le date durante l'attuale regola dell'ora legale.

2
Michel de Ruiter

Sulla base di Colinp_1 post Ho creato una soluzione per convertire un datetime in datetimeoffset che tenga conto di DST e TZ. Spero che sia di aiuto!

DECLARE @offset int -- offset in min
DECLARE @dst bit
DECLARE @appDate datetime

set @dst = 1
set @offset = +60
set @appDate = '2017-04-06 14:21:10.000'

-- output the start and end datetime of DST to the given @appDate
select dateadd(hour, 2, 
                  dateadd(day, 1 - datepart(weekday
                                 , dateadd(day, -1, dateadd(month, 3, dateadd(year, datediff(year, 0, @appDate), 0))))
                                 , dateadd(day, -1, dateadd(month, 3, dateadd(year, datediff(year, 0, @appDate), 0))))) AS 'MEZ -> MESZ'
     , dateadd(hour, 2, 
                  dateadd(day, 1 - datepart(weekday
                                 , dateadd(day, -1, dateadd(month, 10, dateadd(year, datediff(year, 0, @appDate), 0))))
                                 , dateadd(day, -1, dateadd(month, 10, dateadd(year, datediff(year, 0, @appDate), 0))))) AS 'MESZ -> MEZ'

-- output the @appDate as datetimeoffset including offset and DST
SELECT @dst AS 'DST on'
     , @offset AS 'TZ offset'
     , @appDate AS 'originalDate'
     , qDT.isAppDateInDST
     , qDT.datetimeoffset
     , CONVERT(datetime, qDT.datetimeoffset, 1) AS 'UTC'
FROM (
    SELECT 
        CASE WHEN @dst = 1 THEN -- check if DST is needed
           CASE
                WHEN qDST.isAppDateInDST = 1
                THEN TODATETIMEOFFSET(@appDate, @offset + 60) -- add 1 hour to @appDate when its in DST and convert to DATETIMEOFFSET
                ELSE TODATETIMEOFFSET(@appDate, @offset) -- convert to     DATETIMEOFFSET with given offset
        END
    ELSE 
        TODATETIMEOFFSET(@appDate, @offset) -- convert to DATETIMEOFFSET with given offset
        END AS 'datetimeoffset'
      , qDST.isAppDateInDST
    FROM (
        SELECT 
            CASE WHEN @appDate >= dateadd(hour, 2, 
                                    dateadd(day, 1 - datepart(weekday
                                                    , dateadd(day, -1, dateadd(month, 3, dateadd(year, datediff(year, 0, @appDate), 0))))
                                                    , dateadd(day, -1, dateadd(month, 3, dateadd(year, datediff(year, 0, @appDate), 0)))))
                    and @appDate < dateadd(hour, 2, 
                                    dateadd(day, 1 - datepart(weekday
                                                    , dateadd(day, -1, dateadd(month, 10, dateadd(year, datediff(year, 0, @appDate), 0))))
                                                    , dateadd(day, -1, dateadd(month, 10, dateadd(year, datediff(year, 0, @appDate), 0)))))
                THEN 1
            ELSE 0
            END AS 'isAppDateInDST'
    ) qDST
) qDT

GO
0
Mike