Po pierwsze, obsługa czasu i arytmetyka PostgreSQL są fantastyczne, a opcja 3 jest w porządku w ogólnym przypadku. Jest to jednak niepełny widok czasu i stref czasowych i można go uzupełnić:
- Przechowuj nazwę strefy czasowej użytkownika jako preferencję użytkownika (np.
America/Los_AngelesNie -0700).
- Miej dane o zdarzeniach / czasie użytkownika przesyłane lokalnie do ich układu odniesienia (najprawdopodobniej z przesunięciem względem UTC, na przykład
-0700).
- W aplikacji przekonwertuj czas na
UTCi zapisany za pomocą TIMESTAMP WITH TIME ZONEkolumny.
- Zwracaj żądania czasu lokalnego względem strefy czasowej użytkownika (tj. Konwertuj z
UTCna America/Los_Angeles).
- Ustaw bazę danych
timezonena UTC.
Ta opcja nie zawsze działa, ponieważ uzyskanie strefy czasowej użytkownika może być trudne, a zatem porady dotyczące zabezpieczenia, które należy stosować w TIMESTAMP WITH TIME ZONEprzypadku lekkich aplikacji. To powiedziawszy, pozwolę sobie wyjaśnić bardziej szczegółowo niektóre podstawowe aspekty tej opcji 4.
Podobnie jak w przypadku opcji 3, powodem WITH TIME ZONEjest to, że czas, w którym coś się wydarzyło, jest absolutnym momentem w czasie. WITHOUT TIME ZONEzwraca względną strefę czasową. Nigdy, przenigdy nie mieszaj absolutnych i względnych TIMESTAMPÓW.
Z punktu widzenia automatyzacji i spójności upewnij się, że wszystkie obliczenia są wykonywane przy użyciu czasu UTC jako strefy czasowej. Nie jest to wymaganie PostgreSQL, ale pomaga w integracji z innymi językami programowania lub środowiskami. Ustawienie CHECKkolumny w celu upewnienia się, że zapis do kolumny znacznika czasu ma przesunięcie strefy czasowej 0jest pozycją obronną, która zapobiega kilku klasom błędów (np. Skrypt zrzuca dane do pliku, a coś innego sortuje dane czasu za pomocą sortowanie leksykalne). Ponownie, PostgreSQL nie potrzebuje tego do poprawnego obliczania dat lub konwersji między strefami czasowymi (tj. PostgreSQL jest bardzo biegły w przeliczaniu czasów między dowolnymi dwiema dowolnymi strefami czasowymi). Aby upewnić się, że dane wchodzące do bazy danych są przechowywane z przesunięciem równym zero:
CREATE TABLE my_tbl (
my_timestamp TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
CHECK(EXTRACT(TIMEZONE FROM my_timestamp) = '0')
);
test=> SET timezone = 'America/Los_Angeles';
SET
test=> INSERT INTO my_tbl (my_timestamp) VALUES (NOW());
ERROR: new row for relation "my_tbl" violates check constraint "my_tbl_my_timestamp_check"
test=> SET timezone = 'UTC';
SET
test=> INSERT INTO my_tbl (my_timestamp) VALUES (NOW());
INSERT 0 1
Nie jest w 100% doskonały, ale zapewnia wystarczająco silny środek zapobiegający rzucaniu stopą, który zapewnia, że dane są już przekonwertowane na UTC. Istnieje wiele opinii na temat tego, jak to zrobić, ale z mojego doświadczenia wynika, że jest to najlepsze w praktyce.
Krytyka obsługi stref czasowych bazy danych jest w dużej mierze uzasadniona (istnieje wiele baz danych, które radzą sobie z tym z wielką niekompetencją), jednak obsługa znaczników czasu i stref czasowych przez PostgreSQL jest całkiem niezła (pomimo kilku „funkcji” tu i ówdzie). Na przykład jedna taka funkcja:
-- Make sure we're all working off of the same local time zone
test=> SET timezone = 'America/Los_Angeles';
SET
test=> SELECT NOW();
now
-------------------------------
2011-05-27 15:47:58.138995-07
(1 row)
test=> SELECT NOW() AT TIME ZONE 'UTC';
timezone
----------------------------
2011-05-27 22:48:02.235541
(1 row)
Zwróć uwagę, że AT TIME ZONE 'UTC'usuwa informacje o strefie czasowej i tworzy krewnego, TIMESTAMP WITHOUT TIME ZONEużywając ramki odniesienia celu ( UTC).
Podczas konwersji z niekompletnego TIMESTAMP WITHOUT TIME ZONEna a TIMESTAMP WITH TIME ZONE, brakująca strefa czasowa jest dziedziczona z połączenia:
test=> SET timezone = 'America/Los_Angeles';
SET
test=> SELECT EXTRACT(TIMEZONE_HOUR FROM NOW());
date_part
-----------
-7
(1 row)
test=> SELECT EXTRACT(TIMEZONE_HOUR FROM TIMESTAMP WITH TIME ZONE '2011-05-27 22:48:02.235541');
date_part
-----------
-7
(1 row)
-- Now change to UTC
test=> SET timezone = 'UTC';
SET
-- Create an absolute time with timezone offset:
test=> SELECT NOW();
now
-------------------------------
2011-05-27 22:48:40.540119+00
(1 row)
-- Creates a relative time in a given frame of reference (i.e. no offset)
test=> SELECT NOW() AT TIME ZONE 'UTC';
timezone
----------------------------
2011-05-27 22:48:49.444446
(1 row)
test=> SELECT EXTRACT(TIMEZONE_HOUR FROM NOW());
date_part
-----------
0
(1 row)
test=> SELECT EXTRACT(TIMEZONE_HOUR FROM TIMESTAMP WITH TIME ZONE '2011-05-27 22:48:02.235541');
date_part
-----------
0
(1 row)
Podsumowując:
- przechowywać strefę czasową użytkownika jako nazwaną etykietę (np.
America/Los_Angeles), a nie przesunięcie w stosunku do czasu UTC (np. -0700)
- używaj UTC do wszystkiego, chyba że istnieje ważny powód, aby przechowywać niezerowe przesunięcie
- traktuj wszystkie niezerowe czasy UTC jako błąd wejściowy
- nigdy nie mieszaj i nie dopasowuj względnych i bezwzględnych znaczników czasu
- użyj również
UTCjako timezonew bazie danych, jeśli to możliwe
Losowa uwaga dotycząca języka programowania: datetimetyp danych Pythona bardzo dobrze radzi sobie z rozróżnieniem między czasami bezwzględnymi i względnymi (choć początkowo frustrujące, dopóki nie uzupełnisz go biblioteką taką jak PyTZ ).
EDYTOWAĆ
Pozwólcie, że wyjaśnię nieco więcej różnicę między względnym a absolutnym.
Do zarejestrowania zdarzenia używany jest czas bezwzględny. Przykłady: „Zalogowany użytkownik 123” lub „ceremonia ukończenia szkoły rozpocznie się 28 maja 2011 r. O godzinie 14:00 czasu pacyficznego”. Bez względu na lokalną strefę czasową, gdybyś mógł teleportować się do miejsca zdarzenia, byłbyś świadkiem tego wydarzenia. Większość danych dotyczących czasu w bazie danych ma charakter bezwzględny (i dlatego powinien być TIMESTAMP WITH TIME ZONE, najlepiej z przesunięciem +0 i etykietą tekstową reprezentującą zasady rządzące określoną strefą czasową - a nie przesunięciem).
Względnym wydarzeniem byłoby zarejestrowanie lub zaplanowanie czasu czegoś z perspektywy jeszcze nieokreślonej strefy czasowej. Przykłady: „drzwi naszej firmy otwierają się o 8:00 i zamykają o 21:00”, „spotykajmy się w każdy poniedziałek o 7 rano na cotygodniowym spotkaniu śniadaniowym” lub „w każde Halloween o 20:00”. Ogólnie rzecz biorąc, czas względny jest używany w szablonie lub fabryce dla zdarzeń, a czas bezwzględny jest używany do prawie wszystkiego innego. Jest jeden rzadki wyjątek, na który warto zwrócić uwagę, który powinien zilustrować wartość względnych czasów. W przypadku przyszłych wydarzeń, które są wystarczająco odległe w przyszłości, w których może istnieć niepewność co do bezwzględnego czasu, w którym coś może się wydarzyć, użyj względnego znacznika czasu. Oto przykład z prawdziwego świata:
Załóżmy, że jest to rok 2004 i musisz zaplanować dostawę na 31 października 2008 r. O godzinie 13:00 na zachodnim wybrzeżu Stanów Zjednoczonych (tj. America/Los_Angeles/ PST8PDT). Gdyby przechowywać to przy użyciu czasu bezwzględnego ’2008-10-31 21:00:00.000000+00’::TIMESTAMP WITH TIME ZONE, dostawa pojawiłaby się o godzinie 14:00, ponieważ rząd Stanów Zjednoczonych uchwalił ustawę o polityce energetycznej z 2005 r. , Która zmieniła zasady dotyczące czasu letniego. W 2004 r., Kiedy zaplanowano dostawę, datą 10-31-2008byłby czas pacyficzny standardowy ( +8000), ale począwszy od baz danych stref czasowych 2005+ rozpoznano, że 10-31-2008byłby to czas pacyficzny letni (+0700). Przechowywanie względnej sygnatury czasowej ze strefą czasową spowodowałoby prawidłowy harmonogram dostawy, ponieważ względna sygnatura czasowa jest odporna na niewłaściwe manipulowanie przez Kongres. Tam, gdzie granica między używaniem czasów względnych i bezwzględnych do planowania rzeczy jest rozmyta, ale moja zasada jest taka, że planowanie dla czegokolwiek w przyszłości później niż 3-6mo powinno wykorzystywać względne znaczniki czasu (zaplanowane = bezwzględne vs zaplanowane = względny ???).
Drugim / ostatnim typem czasu względnego jest INTERVAL. Przykład: „sesja wygaśnie 20 minut po zalogowaniu się użytkownika”. An INTERVALmożna poprawnie używać z bezwzględnymi znacznikami czasu ( TIMESTAMP WITH TIME ZONE) lub względnymi znacznikami czasu ( TIMESTAMP WITHOUT TIME ZONE). Równie słuszne jest stwierdzenie: „sesja użytkownika wygasa 20 minut po pomyślnym zalogowaniu (login_utc + session_duration)” lub „nasze poranne spotkanie śniadaniowe może trwać tylko 60 minut (recurring_start_time + meeting_length)”.
Ostatnie bity zamieszania: DATE, TIME, TIME WITHOUT TIME ZONEi TIME WITH TIME ZONEsą względne typy danych. Na przykład: '2011-05-28'::DATEreprezentuje datę względną, ponieważ nie masz informacji o strefie czasowej, których można by użyć do określenia północy. Podobnie '23:23:59'::TIMEjest względne, ponieważ nie znasz ani strefy czasowej, ani DATEreprezentowanej przez czas. Nawet z '23:59:59-07'::TIME WITH TIME ZONE, nie wiesz, co DATEby to było. I wreszcie, DATEstrefa czasowa nie jest w rzeczywistości a DATE, to jest TIMESTAMP WITH TIME ZONE:
test=> SET timezone = 'America/Los_Angeles';
SET
test=> SELECT '2011-05-11'::DATE AT TIME ZONE 'UTC';
timezone
---------------------
2011-05-11 07:00:00
(1 row)
test=> SET timezone = 'UTC';
SET
test=> SELECT '2011-05-11'::DATE AT TIME ZONE 'UTC';
timezone
---------------------
2011-05-11 00:00:00
(1 row)
Umieszczanie dat i stref czasowych w bazach danych jest dobrą rzeczą, ale łatwo jest uzyskać nieznacznie nieprawidłowe wyniki. Prawidłowe i pełne przechowywanie informacji o czasie wymaga minimalnego dodatkowego wysiłku, jednak nie oznacza to, że dodatkowy wysiłek jest zawsze wymagany.