risposta-alla-domanda-sullo-sviluppo-web-bd.com

TSQL: come convertire l'ora locale in UTC? (SQL Server 2008)

Abbiamo a che fare con un'applicazione che deve gestire i dati relativi all'ora globale di diversi fusi orari e impostazioni dell'ora legale. L'idea è di archiviare tutto in formato UTC internamente e convertirlo solo avanti e indietro per le interfacce utente localizzate. SQL Server offre meccanismi per gestire le traduzioni in base a un'ora, un paese e un fuso orario?

Questo deve essere un problema comune, quindi sono sorpreso che Google non abbia rivelato nulla di utilizzabile.

Qualche puntatore?

68
BuschnicK

Passarono 7 anni e ...
in realtà c'è questa nuova funzionalità di SQL Server 2016 che fa esattamente ciò di cui hai bisogno.
Si chiama AT FUSO ORARIO e converte la data in un fuso orario specificato considerando le modifiche dell'ora legale (ora legale).
Ulteriori informazioni qui: https://msdn.Microsoft.com/en-us/library/mt612795.aspx

34
Piotr Owsiak

Funziona con date che attualmente hanno lo stesso offset UTC dell'host di SQL Server; non tiene conto delle modifiche dell'ora legale. Sostituisci YOUR_DATE Con la data locale da convertire.

SELECT DATEADD(second, DATEDIFF(second, GETDATE(), GETUTCDATE()), YOUR_DATE);

59
shaig

Mentre alcune di queste risposte ti porteranno nel campo da baseball, non puoi fare ciò che stai cercando di fare con date arbitrarie per SqlServer 2005 e precedenti a causa dell'ora legale. L'uso della differenza tra l'attuale UTC locale e quello attuale mi darà l'offset come esiste oggi. Non ho trovato un modo per determinare quale sarebbe stato l'offset per la data in questione.

Detto questo, so che SqlServer 2008 fornisce alcune nuove funzioni di data che potrebbero risolvere tale problema, ma le persone che utilizzano una versione precedente devono essere consapevoli delle limitazioni.

Il nostro approccio è di mantenere l'UTC ed eseguire la conversione sul lato client, dove abbiamo un maggiore controllo sull'accuratezza della conversione.

21
user890155

SQL Server 2008 ha un tipo chiamato datetimeoffset. È davvero utile per questo tipo di cose.

http://msdn.Microsoft.com/en-us/library/bb630289.aspx

Quindi puoi utilizzare la funzione SWITCHOFFSET per spostarlo da un fuso orario a un altro, mantenendo comunque lo stesso valore UTC.

http://msdn.Microsoft.com/en-us/library/bb677244.aspx

Rapinare

14
Rob Farley

È possibile utilizzare il mio progetto SQL Server Time Zone Support per convertire tra i fusi orari standard IANA, come elencato qui .

UTC to Local è così:

SELECT Tzdb.UtcToLocal('2015-07-01 00:00:00', 'America/Los_Angeles')

Local to UTC è così:

SELECT Tzdb.LocalToUtc('2015-07-01 00:00:00', 'America/Los_Angeles', 1, 1)

Le opzioni numeriche sono contrassegnate per controllare il comportamento quando i valori dell'ora locale sono influenzati dall'ora legale. Questi sono descritti in dettaglio nella documentazione del progetto.

14

Ecco il codice per convertire una zona DateTime in un'altra zona DateTime

DECLARE @UTCDateTime DATETIME = GETUTCDATE();
DECLARE @ConvertedZoneDateTime DATETIME;

-- 'UTC' to 'India Standard Time' DATETIME
SET @ConvertedZoneDateTime = @UTCDateTime AT TIME ZONE 'UTC' AT TIME ZONE 'India Standard Time'
SELECT @UTCDateTime AS UTCDATE,@ConvertedZoneDateTime AS IndiaStandardTime

-- 'India Standard Time' to 'UTC' DATETIME
SET @UTCDateTime = @ConvertedZoneDateTime AT TIME ZONE 'India Standard Time' AT TIME ZONE 'UTC'
SELECT @ConvertedZoneDateTime AS IndiaStandardTime,@UTCDateTime AS UTCDATE

Nota : AT TIME ZONEfunziona solo su SQL Server 2016 + e il vantaggio è che considera automaticamente la luce del giorno durante la conversione in un particolare fuso orario

10
KarthikeyanMlp

Tendo ad usare DateTimeOffset per tutto lo spazio di archiviazione di data e ora che non è correlato a un evento locale (ad esempio: meeting/party, ecc., 12: 00-15: 00 al museo).

Per ottenere l'attuale DTO come UTC:

DECLARE @utcNow DATETIMEOFFSET = CONVERT(DATETIMEOFFSET, SYSUTCDATETIME())
DECLARE @utcToday DATE = CONVERT(DATE, @utcNow);
DECLARE @utcTomorrow DATE = DATEADD(D, 1, @utcNow);
SELECT  @utcToday [today]
        ,@utcTomorrow [tomorrow]
        ,@utcNow [utcNow]

NOTA: userò sempre UTC quando invio via cavo ... JS lato client può facilmente arrivare a/da UTC locale. Vedi: new Date().toJSON() ...

Il JS seguente gestirà l'analisi di una data UTC/GMT in formato ISO8601 in un datetime locale.

if (typeof Date.fromISOString != 'function') {
  //method to handle conversion from an ISO-8601 style string to a Date object
  //  Date.fromISOString("2009-07-03T16:09:45Z")
  //    Fri Jul 03 2009 09:09:45 GMT-0700
  Date.fromISOString = function(input) {
    var date = new Date(input); //EcmaScript5 includes ISO-8601 style parsing
    if (!isNaN(date)) return date;

    //early shorting of invalid input
    if (typeof input !== "string" || input.length < 10 || input.length > 40) return null;

    var iso8601Format = /^(\d{4})-(\d{2})-(\d{2})((([T ](\d{2}):(\d{2})(:(\d{2})(\.(\d{1,12}))?)?)?)?)?([Zz]|([-+])(\d{2})\:?(\d{2}))?$/;

    //normalize input
    var input = input.toString().replace(/^\s+/,'').replace(/\s+$/,'');

    if (!iso8601Format.test(input))
      return null; //invalid format

    var d = input.match(iso8601Format);
    var offset = 0;

    date = new Date(+d[1], +d[2]-1, +d[3], +d[7] || 0, +d[8] || 0, +d[10] || 0, Math.round(+("0." + (d[12] || 0)) * 1000));

    //use specified offset
    if (d[13] == 'Z') offset = 0-date.getTimezoneOffset();
    else if (d[13]) offset = ((parseInt(d[15],10) * 60) + (parseInt(d[16],10)) * ((d[14] == '-') ? 1 : -1)) - date.getTimezoneOffset();

    date.setTime(date.getTime() + (offset * 60000));

    if (date.getTime() <= new Date(-62135571600000).getTime()) // CLR DateTime.MinValue
      return null;

    return date;
  };
}
4
Tracker1

Sì, in una certa misura come dettagliato qui .
L'approccio che ho usato (prima del 2008) è quello di fare la conversione nella logica di business .NET prima di inserirla nel DB.

3
AdaTheDev

Puoi utilizzare la funzione GETUTCDATE () per ottenere il datetime UTC Probabilmente puoi selezionare la differenza tra GETUTCDATE () e GETDATE () e usare questa differenza per regolare le tue date su UTC

Ma sono d'accordo con il messaggio precedente, che è molto più facile controllare il giusto datetime nel livello aziendale (in .NET, ad esempio).

2
Bogdan_Ch