Jak poprawnie obsługiwać TimeZone w SQL Server?

Mój lokalny serwer programistyczny znajduje się na Bliskim Wschodzie, ale mój serwer produkcyjny znajduje się w Wielkiej Brytanii.

Muszę pokazać użytkownikowi datę w strefie czasowej. Na przykład, jeśli użytkownik jest w Arabii Saudyjskiej, muszę pokazać czas zgodnie z formatem Arabii Saudyjskiej.

Czy powinienem utworzyć nową tabelę bazy danych o nazwie TimeZone i zapisać czas w UTC?

Niestety nie można tego szybko naprawić. Internacjonalizacja aplikacji powinna być częścią pierwszych dyskusji projektowych, ponieważ tak naprawdę dotyczy ona wielu różnych obszarów, w tym porównań daty i godziny oraz formatowania danych wyjściowych.

W każdym razie, aby przejść na ścieżkę Doing It Right, należy przechowywać informacje o strefie czasowej wraz z czasem . Innymi słowy, uświadomienie sobie, że data / czas nie20130407 14:50 ma znaczenia bez (a) uwzględnienia aktualnego wówczas przesunięcia UTC strefy czasowej ^{(uwaga 1)} lub (b) upewnienia się, że cała logika wstawiająca te wartości najpierw przekształca się w pewne ustalone przesunięcie ( najprawdopodobniej 0). Bez tych dwóch wartości dwie podane wartości czasu są nieporównywalne , a dane są uszkodzone . (Nawiasem mówiąc, ta druga metoda polega na graniu ogniem ^{(uwaga 2)} ; nie rób tego.)

W SQL Server 2008+ można przechowywać przesunięcie z czasem bezpośrednio, używając datetimeoffsettypu danych. (Dla kompletności, w 2005 r. I wcześniej dodałbym drugą kolumnę do przechowywania aktualnej wówczas wartości przesunięcia UTC (w minutach).)

Ułatwia to aplikację typu desktop, ponieważ platformy te zwykle mają mechanizmy do automatycznego konwertowania daty / godziny + strefy czasowej na czas lokalny, a następnie formatowania danych wyjściowych, wszystko na podstawie ustawień regionalnych użytkownika.

W przypadku sieci, która jest z natury odłączoną architekturą, nawet przy prawidłowo skonfigurowanych danych zaplecza, jest bardziej złożona, ponieważ potrzebujesz informacji o kliencie, aby móc przeprowadzić konwersję i / lub formatowanie. Zwykle odbywa się to za pomocą ustawień preferencji użytkownika (aplikacja konwertuje / formatuje rzeczy przed wyjściem) lub po prostu pokazuje rzeczy z tym samym stałym formatem i przesunięciem strefy czasowej dla wszystkich (co obecnie robi platforma Stack Exchange).

Możesz zobaczyć, jak jeśli dane zaplecza nie zostaną poprawnie skonfigurowane, bardzo szybko stanie się skomplikowane i zhackowane. Nie polecałbym zejść żadną z tych ścieżek, ponieważ po prostu skończy się więcej problemów.

Notatka 1:

Przesunięcie UTC strefy czasowej nie jest stałe: rozważ oszczędności w świetle dziennym, gdy przesunięcie UTC strefy różni się o plus lub minus godzinę. Również daty i godziny letni w strefach różnią się regularnie. Zatem użycie datetimeoffset(lub połączenie local timei UTC offset at that time) zapewnia maksymalne odzyskiwanie informacji.

Uwaga 2:

Chodzi o kontrolowanie danych wejściowych. Chociaż nie ma niezawodnego sposobu sprawdzania poprawności wartości przychodzących, lepiej jest wprowadzić prosty standard, który nie wymaga obliczeń. Jeśli publiczny interfejs API oczekuje typu danych zawierającego przesunięcie, wymaganie to będzie jasne dla osoby dzwoniącej.

Jeśli tak nie było, osoba dzwoniąca musi polegać na dokumentacji (jeśli ją czyta) lub obliczenia są wykonywane niepoprawnie itp. Istnieje mniej trybów awarii / błędów wymagających przesunięcia, w szczególności w przypadku systemu rozproszonego ( lub nawet po prostu sieć / baza danych na oddzielnych serwerach, jak w tym przypadku).

Przechowywanie przesunięcia i tak zabija dwa ptaki jednym kamieniem; i nawet jeśli nie jest to teraz wymagane , umożliwia to później, jeśli to konieczne. To prawda, że ​​zajmuje więcej miejsca, ale myślę, że warto go wymienić, ponieważ dane zostaną utracone, jeśli nigdy nie zostaną zapisane.

Opracowałem kompleksowe rozwiązanie do konwersji stref czasowych w SQL Server. Zobacz obsługę stref czasowych programu SQL Server w witrynie GitHub .

Prawidłowo obsługuje konwersje między strefami czasowymi i do UTC, w tym czas letni.

Jest podobny w koncepcji do rozwiązania „T-SQL Toolbox” opisanego w odpowiedzi na reklamy, ale wykorzystuje bardziej typowe strefy czasowe IANA / Olson / TZDB zamiast Microsoft i zawiera narzędzia do przechowywania danych jako nowych wydań TZDB wychodzi.

Przykładowe użycie (aby odpowiedzieć na PO):

SELECT Tzdb.UtcToLocal(sysutcdatetime(), 'Asia/Riyadh') as CurrentTimeInSaudiArabia

Zobacz readme na GitHub, aby uzyskać dodatkowe interfejsy API i szczegółowe wyjaśnienia.

Należy również pamiętać, że ponieważ jest to rozwiązanie oparte na UDF, nie jest zaprojektowane z wydajnością jako głównym celem. Byłoby jeszcze lepiej, gdyby ta funkcja była wbudowana w SQL Server, podobnie jak CONVERT_TZfunkcja istnieje w Oracle i MySQL.

SQL Server wprowadził zmiany w 2005 r., Gdzie wewnętrzna strefa czasowa jest zapisywana w UTC. Było to w dużej mierze spowodowane replikacją geograficzną i projektorami HA obejmującymi wysyłanie kłód, a zapisanie czasów transportu kłód w różnych strefach czasowych uniemożliwiło przywrócenie ich starej metody.

W ten sposób zapisanie wszystkiego wewnętrznie w czasie UTC pozwoliło SQL Serverowi działać dobrze na całym świecie. Jest to jeden z powodów, dla których oszczędzanie światła dziennego jest rodzajem bólu w systemie Windows, ponieważ inne produkty MS, takie jak Outlook, również zapisują wewnętrznie datę / czas jako UTC i tworzą przesunięcie, które należy załatać.

Pracuję w firmie, w której mamy tysiące serwerów (choć nie serwery MS SQL, ale wszelkiego rodzaju serwery) rozproszonych po całym świecie, a gdybyśmy specjalnie nie zmuszali UTC do wszystkiego, wszyscy oszalalibyśmy bardzo szybko.

Opracowałem i opublikowałem projekt „T-SQL Toolbox” na codeplex, aby pomóc każdemu, kto zmaga się z obsługą czasu i strefy czasowej w SQL Server. Jest open source i całkowicie darmowy.

Oferuje łatwe UDF do konwersji danych za pomocą zwykłego T-SQL z dodatkowymi tabelami konfiguracji po wyjęciu z pudełka.

W twoim przykładzie możesz użyć następującej próbki:

SELECT [DateTimeUtil].[UDF_ConvertLocalToLocalByTimezoneIdentifier] (
    'GMT Standard Time', -- the original timezone in which your datetime is stored
    'Middle East Standard Time', -- the target timezone for your user
    '2014-03-30 01:55:00' -- the original datetime you want to convert
)

Zwróci to skonwertowaną wartość daty / godziny dla użytkownika.

Lista wszystkich obsługiwanych stref czasowych znajduje się w tabeli „DateTimeUtil.Timezone” również w bazie danych T-SQL Toolbox.

Być może brakuje mi czegoś oczywistego, ale jeśli wszystko, co chcesz zrobić, to wyświetlić datę w strefie czasowej użytkownika i formacie językowym, najprostszym sposobem jest zawsze przechowywanie dat w UTC w bazie danych i umożliwienie konwersji aplikacji klienckiej z UTC na lokalną strefę czasową i użyj regionalnych ustawień użytkownika, aby odpowiednio sformatować datę.