Jaki typ znacznika czasu wybrać w bazie danych PostgreSQL?

Po pierwsze, obsługa czasu i arytmetyka PostgreSQL jest fantastyczna, a Opcja 3 jest dobra w ogólnym przypadku. Jest to jednak Niepełny Widok czasu i stref czasowych i może być uzupełniony:

1. Zapisz nazwę strefy czasowej użytkownika jako preferencję użytkownika(np. America/Los_Angeles, nie -0700).
2. mają dane zdarzeń/czasu użytkownika przesyłane lokalnie do ich ramki odniesienia(najprawdopodobniej przesunięcie od UTC, takie jak -0700).
3. W aplikacji Przelicz czas na UTC i przechowywany przy użyciu kolumny TIMESTAMP WITH TIME ZONE.
4. Return time requests local to a user ' s time zone (tj. convert from UTC to America/Los_Angeles).
5. Ustaw timezone w bazie danych na UTC.

Ta opcja nie zawsze działa, ponieważ może być trudno uzyskać strefę czasową użytkownika, a tym samym porady dotyczące zabezpieczeń, aby używać TIMESTAMP WITH TIME ZONE dla lekkich aplikacji. Niemniej jednak pozwolę sobie bardziej szczegółowo wyjaśnić niektóre aspekty tego wariantu 4.

Podobnie jak Opcja 3, powodem WITH TIME ZONE jest to, że czas, w którym coś się stało jest absolutnym momentem w czasie. WITHOUT TIME ZONE daje względną strefę czasową. Nigdy, przenigdy nie mieszaj absolutnych i względnych znaczników czasu.

Z punktu widzenia programowania i spójności, upewnij się, że wszystkie obliczenia są wykonywane przy użyciu UTC jako strefy czasowej. Nie jest to wymóg PostgreSQL, ale pomaga w integracji z innymi językami lub środowiskami programowania. Ustawienie CHECK na kolumnie, aby upewnić się, że zapis do czasu kolumna stempla ma przesunięcie strefy czasowej 0 jest pozycją obronną, która zapobiega kilku klasom błędów (np. skrypt zrzuca dane do pliku, a coś innego sortuje dane czasowe za pomocą sortowania leksykalnego). Ponownie, PostgreSQL nie potrzebuje tego, aby poprawnie wykonywać obliczenia daty lub konwertować między strefami czasowymi (tzn. PostgreSQL jest bardzo biegły w konwertowaniu czasów między dowolnymi dwiema dowolnymi strefami czasowymi). Aby zapewnić, że dane trafiające do bazy danych są przechowywane z przesunięciem zerowym:

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

To Nie w 100% idealny, ale zapewnia wystarczająco silny środek zapobiegający footshootingowi, który zapewnia, że dane są już przekonwertowane na UTC. Jest wiele opinii na temat tego, jak to zrobić, ale wydaje się to być najlepsze w praktyce z mojego doświadczenia.

Krytyka obsługi stref czasowych baz danych jest w dużej mierze uzasadniona( istnieje wiele baz danych, które obsługują to z wielką niekompetencją), jednak obsługa znaczników czasu i stref czasowych PostgreSQL jest całkiem niesamowita (pomimo kilku "funkcji" tutaj i tam). Na przykład jedna z takich funkcji:

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

Zauważ, że AT TIME ZONE 'UTC' usuwa informacje o strefie czasowej i tworzy relatywny TIMESTAMP WITHOUT TIME ZONE używając ramki odniesienia (UTC).

Podczas konwersji z niekompletnego TIMESTAMP WITHOUT TIME ZONE na 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)

The bottom line:

Strefa czasowa użytkownika jest przechowywana jako nazwa etykiety (np. America/Los_Angeles), a nie offset od UTC (np. -0700)
• użyj UTC do wszystkiego, chyba że istnieje przekonujący powód do przechowywania niezerowego offsetu
• traktuj wszystkie niezerowe czasy UTC jako błąd wejściowy
• nigdy nie mieszaj i dopasuj względnych i bezwzględnych znaczników czasu
• użyj również UTC jako timezone w bazie danych, jeśli to możliwe

Losowy język programowania Uwaga: Python ' s datetime typ danych jest bardzo dobry w utrzymaniu rozróżnienia między bezwzględnymi a względnymi czasami (choć frustrujące na początku, dopóki nie uzupełnisz go o bibliotekę taką jak PyTZ ).

EDIT

Pozwól, że wyjaśnię nieco różnicę między względnym a absolutnym.

Czas bezwzględny jest używany do rejestrowania zdarzenia. Przykłady: "użytkownik 123 zalogowany" lub " uroczyste zakończenie Roku Szkolnego 2011-05-28 14: 00 PST."Niezależnie od lokalnej strefy czasowej, jeśli możesz się teleportować do miejsca zdarzenia, możesz być świadkiem wydarzenia. Większość danych w bazie danych jest bezwzględna (i dlatego powinna być TIMESTAMP WITH TIME ZONE, najlepiej z a + 0 offset i etykieta tekstowa reprezentująca reguły rządzące określoną strefą czasową-nie offset).

Relatywnym wydarzeniem byłoby zarejestrowanie lub zaplanowanie czasu czegoś z perspektywy jeszcze nie ustalonej strefy czasowej. Przykłady: "drzwi naszej firmy otwierają się o 8 rano i zamykają o 21: 00", "spotkajmy się w każdy poniedziałek o 7 rano na cotygodniowe spotkanie śniadaniowe" lub " każde Halloween o 20: 00."Ogólnie 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, który warto zwrócić uwagę, który powinien zilustrować wartość względnych czasów. W przypadku przyszłych zdarzeń, które są wystarczająco daleko w przyszłości, gdzie może istnieć niepewność co do bezwzględnego czasu, w którym coś może się wydarzyć, użyj znacznika czasu względnego. Oto przykład z prawdziwego świata:

Załóżmy, że jest rok 2004 i musisz zaplanować dostawę 31 października 2008 o godzinie 13 na Zachodnim Wybrzeżu USA (tj. America/Los_Angeles/PST8PDT). Jeśli 14: 00, ponieważ rząd USA uchwalił ustawę o polityce energetycznej z 2005 roku, która zmieniła zasady regulujące czas letni. W 2004 roku, kiedy dostawa była zaplanowana, datą 10-31-2008 byłby Pacific Standard Time (+8000), ale począwszy od roku 2005+ bazy danych strefy czasowej uznały, że 10-31-2008 byłby Pacific Daylight Savings time (+0700). Przechowywanie znacznika czasu względnego ze strefą czasową wynikałoby to z poprawnego harmonogramu dostaw, ponieważ względny znacznik czasu jest odporny na nielegalne manipulacje Kongresu. Gdzie granica między używaniem względnych a bezwzględnych czasów do planowania rzeczy jest, jest rozmyta linia, ale moja zasada jest, że planowanie dla czegokolwiek w przyszłości dalej niż 3-6mo powinno używać względnych znaczników czasu (zaplanowane = absolutne vs planowane = względne ???).

Drugim / ostatnim typem czasu względnego jest INTERVAL. Przykład: "sesja będzie CZAS 20 minut po zalogowaniu się użytkownika". An INTERVAL może być poprawnie używany z bezwzględnymi znacznikami czasu (TIMESTAMP WITH TIME ZONE) lub względnymi znacznikami czasu (TIMESTAMP WITHOUT TIME ZONE). Równie poprawne jest stwierdzenie: "sesja użytkownika wygasa 20 minut po pomyślnym zalogowaniu (login_utc + session_duration)" lub " nasze poranne spotkanie na śniadanie może trwać tylko 60 minut (recurring_start_time + meeting_length)".

Last bits of confusion: DATE, TIME, TIME WITHOUT TIME ZONE i TIME WITH TIME ZONE są względnymi typami danych. Na przykład: '2011-05-28'::DATE reprezentuje a względna Data, ponieważ nie masz informacji o strefie czasowej, która mogłaby zostać użyta do identyfikacji północy. Podobnie, {[45] } jest względne, ponieważ nie znasz ani strefy czasowej, ani DATE reprezentowanej przez czas. Nawet z '23:59:59-07'::TIME WITH TIME ZONE, nie wiesz, co by było DATE. I wreszcie, DATE ze strefą czasową nie jest w rzeczywistości DATE, jest to 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ć subtelnie nieprawidłowe wyniki. Minimal dodatkowy wysiłek jest wymagany do prawidłowego i całkowitego przechowywania informacji o czasie, jednak nie oznacza to, że dodatkowy wysiłek jest zawsze wymagany.