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


82

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?


Mój serwer mssql jest połączony z serwerem mysql. Zastanawiam się, czy można uruchomić mysql CONVERT_TZ (time, srczone, dstzone) na zapytaniach :-) Dziwnie brakuje tej funkcji; jest wbudowany w Linuksa.
Leif Neland

1
@BuschnicK Zobacz moją odpowiedź poniżej. Właściwie myślę, że możesz to zaakceptować, nawet jeśli innym jest łatwiej znaleźć.
Piotr Owsiak

Krótka odpowiedź: nie ma wbudowanego sposobu, aby to zrobić przed SQL Server 2016, więc będzie wymagał niestandardowego kodu we wcześniejszych wersjach.
lehiester

Średnia odpowiedź: wszystkie funkcje przesunięcia czasowego przed SQL Server 2016 działały tylko z przesunięciami bezwzględnymi, bez obsługi przesunięć zmiennych, które występują w większości stref czasowych ze względu na czas letni. Zapytania nie mają żadnego sposobu na dostęp do przesunięć czasowych, z wyjątkiem tego, co jest bieżącym przesunięciem czasu lokalnego serwera, co jest bezużyteczne przy próbach zautomatyzowania konwersji.
lehiester

Odpowiedzi:


49

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


19
Nie pracuję już nad tym - nie w tym projekcie, nie w SQL Server, nie w tej samej firmie ani nawet w tym samym kraju ;-) Więc to mi nie pomoże, ale zagłosuję za tym, którzy znajdą to pytanie teraz.
BuschnicK

2
@BuschnicK tak, myślę, ale przyszedłem tutaj szukając rozwiązania tego samego problemu, co ty, więc postanowiłem wysłać odpowiedź teraz, gdy jest prawdziwe rozwiązanie: D
Piotr Owsiak

5
Pytanie dotyczy programu SQL Server 2008. Zaktualizuj pytanie, jeśli akceptujesz tę odpowiedź. Dzięki
Robert

1
Aby przekonwertować na UTC, możesz zrobić „ATIME ZONE” UTC.
Krzyserious

1
@Piotr Owsiak: Tak, ale zakłada się, że czas wejściowy to UTC, co jest bezcelowe, jeśli chcesz przeliczyć czas lokalny na UTC ... Tak więc minęło 7 lat, a oni nadal nie uważają, że warto to właściwie obchodzić. ..
Stefan Steiger

65

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


2
Dzięki, to dobry pomysł, ale działa tylko w jednej strefie czasowej - na komputerze lokalnym. Potrzebujemy go jednak do pracy w dowolnych
strefach

51
To nie uwzględnia czasu letniego
Gabriel McAdams

10
Nie! Różnica zależy od dokładnej daty. To zależy od czasu letniego.
usr

3
W moim przypadku potrzebowałem tylko 1 strefy czasowej i to działało świetnie! Dzięki.
M Thelen,

10
To działa dobrze, jeśli wiesz, że biegasz dzisiaj, a nie historycznie, dzięki
David Adlington,

22

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.


16

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.


1
Jak stary ... ten projekt Matta jest świetny i nie wymaga CLR. Matt zasługuje na wielkie uznanie za to +100
buckley

14

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ć


6
SWITCHOFFSET nie uwzględnia czasu letniego, więc jest przydatny tylko w niektórych sytuacjach.
robocat

2
Nie. Ale pytanie dotyczyło możliwości przełączenia na dowolną żądaną strefę czasową.
Rob Farley

Z pytania „różne strefy czasowe i ustawienia czasu letniego”. My również szukamy rozwiązania na lokalne czasy. Twoja sugestia nie rozwiązuje problemu czasu letniego, prawda?
robocat

Za każdym razem, gdy trzeba wykryć strefę czasową na kliencie, a następnie ustawić czasy powrotu bazy danych w określonej strefie czasowej, bardzo przydatna jest funkcja SWITCHOFFSET.
Rob Farley

3
@RobFarley Wykrywanie strefy czasowej na kliencie i używanie SWITCHOFFSET może nadal powodować problemy. Musisz wiedzieć, czy do daty i godziny konwertowanej zastosowano czas letni, czy nie. Po prostu wykrycie strefy czasowej i zastosowanie bieżącego przesunięcia do UTC może oznaczać godzinę przerwy - i to w prostym przypadku, gdy wszystkie konwersje są w tym samym kraju. Nie każdy kraj przełącza się na czas letni / zimowy w tych samych dniach. SWITCHOFFSET działa dobrze, jeśli przechowujesz czas lokalny i znasz różnicę między oryginalną a docelową strefą w tym samym kraju.
Jamie Zobacz

12

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


2
Uwielbiam to, jeśli nie z innego powodu niż fakt, że pokazuje, że możesz łączyć ze sobą wiele AT TIME ZONEpołączeń (fraz?)! Po prostu eleganckie. Powiedziałem wcześniej, że stackoverflow.com/a/44579178/112764 spełnił moje potrzeby, ale to jest jeszcze lepsze. Major kudos.
NateJ

DECLARE @UTCDateTime DATETIME = GETUTCDATE(); DECLARE @ConvertedZoneDateTime DATETIME; -- 'UTC' to 'India Standard Time' to 'Eastern Standard Time' DATETIME SET @ConvertedZoneDateTime = @UTCDateTime AT TIME ZONE 'UTC' AT TIME ZONE 'India Standard Time' AT TIME ZONE 'Eastern Standard Time' SELECT @UTCDateTime AS UTCDATE,@ConvertedZoneDateTime AS EasternStandardTime Tak, możesz łączyć wiele AT TIME ZONEpołączeń, ale Od i Do wystarczą do każdej konwersji, a większość potrzebowaliśmy
KarthikeyanMlp

4

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

+1 Ja też przełączyłem się na DateTimeOffset. Pozwala uniknąć wielu problemów z lokalnymi konwersjami UTC +. Jednak z podobnych powodów polecam również przesyłanie wartości z przesunięciem over-the-wire (przez JSON).
user2864740

Uwaga: robię dokładnie to, co robisz na odwrót. W przypadku DateTime, które są powiązane ze zdarzeniem lokalnym, przechowuję DateTimeOffset. W przypadku DateTime, który nie jest powiązany ze zdarzeniem lokalnym, przechowuję DateTime w formacie UTC. Pierwsza ma dwa istotne punkty danych (kiedy jest w czasie lokalnym i jaki to czas lokalny), druga tylko jeden (kiedy jest)
Martijn

@Martijn Ale strefa czasowa nie podaje lokalizacji, a mimo to musisz przechowywać je oddzielnie.
Tracker1,

@ tracker1 Działa to tylko wtedy, gdy lokalizacja ma sposób na poznanie swojej strefy czasowej, a nawet wtedy konwersja jest bardzo trudna.
Martijn,

3

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.


1

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).


12
Nie! Różnica zależy od dokładnej daty. To zależy od czasu letniego.
usr

3
Nie uwzględnia czasu letniego. Używałem takich rozwiązań przez jakiś czas i spowodowało to poważne problemy. Musisz określić, czy data, z którą porównujesz, to czas letni.
Jeff Davis

-1

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
Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.