TSQL: Jak przekonwertować czas lokalny na UTC? (SQL Server 2008)

Mamy do czynienia z aplikacją, która musi obsługiwać globalne dane czasowe z różnych stref czasowych i ustawień czasu letniego. Chodzi o to, aby przechowywać wszystko w formacie UTC wewnętrznie i konwertować tylko tam iz powrotem dla zlokalizowanych interfejsów użytkownika. Czy SQL Server oferuje jakiekolwiek mechanizmy obsługi tłumaczeń w danym czasie, kraju i strefie czasowej?

To musi być powszechny problem, więc jestem zaskoczony, że Google nie znalazł niczego użytecznego.

Jakieś wskazówki?

Minęło 7 lat i ...
faktycznie pojawiła się nowa funkcja SQL Server 2016, która robi dokładnie to, czego potrzebujesz.
Nazywa się AT TIME ZONE i konwertuje datę na określoną strefę czasową z uwzględnieniem zmian czasu letniego (DST).
Więcej informacji tutaj: https://msdn.microsoft.com/en-us/library/mt612795.aspx

Działa to w przypadku dat, które obecnie mają takie samo przesunięcie czasu UTC jak host SQL Server; nie uwzględnia zmian czasu letniego. Zastąp YOUR_DATElokalną datą konwersji.

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

Chociaż kilka z tych odpowiedzi doprowadzi cię do sedna, nie możesz zrobić tego, co próbujesz zrobić z arbitralnymi datami dla SqlServer 2005 i wcześniejszych ze względu na czas letni. Użycie różnicy między bieżącym lokalnym i bieżącym UTC da mi przesunięcie, jakie istnieje dzisiaj. Nie znalazłem sposobu, aby określić, jakie byłoby przesunięcie dla danego dnia.

To powiedziawszy, wiem, że SqlServer 2008 zapewnia kilka nowych funkcji daty, które mogą rozwiązać ten problem, ale osoby używające wcześniejszej wersji muszą być świadome ograniczeń.

Nasze podejście polega na utrzymaniu UTC i przeprowadzaniu konwersji po stronie klienta, gdzie mamy większą kontrolę nad dokładnością konwersji.

W przypadku programu SQL Server 2016 i nowszych oraz Azure SQL Database użyj wbudowanej AT TIME ZONEinstrukcji .

W przypadku starszych wersji programu SQL Server można użyć mojego projektu obsługi stref czasowych programu SQL Server do konwersji między standardowymi strefami czasowymi IANA, jak wymieniono tutaj .

UTC to Local wygląda tak:

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

Lokalne dla UTC wygląda tak:

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

Opcje liczbowe są flagami służącymi do kontrolowania zachowania, gdy na wartości czasu lokalnego wpływa czas letni. Są one szczegółowo opisane w dokumentacji projektowej.

SQL Server 2008 ma typ o nazwie datetimeoffset. Jest to naprawdę przydatne do tego typu rzeczy.

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

Następnie możesz użyć tej funkcji, SWITCHOFFSETaby przenieść ją z jednej strefy czasowej do drugiej, zachowując tę ​​samą wartość UTC.

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

Obrabować

Oto kod do konwersji jednej strefy DateTimedo innej strefyDateTime

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

Uwaga : AT TIME ZONE działa tylko w SQL Server 2016+, a zaletą jest to, że automatycznie uwzględnia światło dzienne podczas konwersji do określonej strefy czasowej

Skłaniam się ku używaniu DateTimeOffset do przechowywania wszystkich dat, które nie są związane z wydarzeniem lokalnym (np. Spotkanie / impreza itp., 12: 00-15: 00 w muzeum).

Aby uzyskać aktualne DTO w formacie 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]

UWAGA: Zawsze będę używał UTC podczas przesyłania przez sieć ... JS po stronie klienta może łatwo dostać się do / z lokalnego UTC. Zobacz: new Date().toJSON()...

Poniższy JS zajmie się analizowaniem daty UTC / GMT w formacie ISO8601 na lokalną datę i godzinę.

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;
  };
}

Tak, do pewnego stopnia, jak opisano tutaj .
Podejście, którego użyłem (przed 2008), polega na wykonaniu konwersji w logice biznesowej .NET przed wstawieniem do bazy danych.

Możesz użyć funkcji GETUTCDATE (), aby uzyskać datetime UTC Prawdopodobnie możesz wybrać różnicę między GETUTCDATE () i GETDATE () i użyć tej różnicy do dostosowania swoich dat do UTC

Zgadzam się jednak z poprzednim przesłaniem, że o wiele łatwiej jest kontrolować właściwą datę i godzinę w warstwie biznesowej (np. W .NET).

Przykładowe użycie:

SELECT
    Getdate=GETDATE()
    ,SysDateTimeOffset=SYSDATETIMEOFFSET()
    ,SWITCHOFFSET=SWITCHOFFSET(SYSDATETIMEOFFSET(),0)
    ,GetutcDate=GETUTCDATE()
GO

Zwroty:

Getdate SysDateTimeOffset   SWITCHOFFSET    GetutcDate
2013-12-06 15:54:55.373 2013-12-06 15:54:55.3765498 -08:00  2013-12-06 23:54:55.3765498 +00:00  2013-12-06 23:54:55.373