Różnica między znacznikami czasu ze / bez strefy czasowej w PostgreSQL

Czy wartości znaczników czasu są przechowywane inaczej w PostgreSQL, gdy typ danych jest WITH TIME ZONEkontra WITHOUT TIME ZONE? Czy różnice można zilustrować za pomocą prostych przypadków testowych?

Różnice omówiono w dokumentacji PostgreSQL dla typów daty / godziny . Tak, leczenie TIMElub TIMESTAMPróżni się między jednym WITH TIME ZONElubWITHOUT TIME ZONE . Nie wpływa to na sposób przechowywania wartości; wpływa na sposób ich interpretacji.

Wpływ stref czasowych na te typy danych jest szczegółowo opisany w dokumentach. Różnica wynika z tego, co system może rozsądnie wiedzieć o wartości:

• Gdy strefa czasowa jest częścią wartości, wartość może być renderowana jako czas lokalny w kliencie.

• Bez strefy czasowej jako części wartości oczywistą domyślną strefą czasową jest UTC, więc jest renderowana dla tej strefy czasowej.

Zachowanie różni się w zależności od co najmniej trzech czynników:

• Ustawienie strefy czasowej w kliencie.
• Typ danych (tj. WITH TIME ZONELub WITHOUT TIME ZONE) wartości.
• Określa, czy wartość jest określona w określonej strefie czasowej.

Oto przykłady obejmujące kombinacje tych czynników:

foo=> SET TIMEZONE TO 'Japan';
SET
foo=> SELECT '2011-01-01 00:00:00'::TIMESTAMP;
      timestamp      
---------------------
 2011-01-01 00:00:00
(1 row)

foo=> SELECT '2011-01-01 00:00:00'::TIMESTAMP WITH TIME ZONE;
      timestamptz       
------------------------
 2011-01-01 00:00:00+09
(1 row)

foo=> SELECT '2011-01-01 00:00:00+03'::TIMESTAMP;
      timestamp      
---------------------
 2011-01-01 00:00:00
(1 row)

foo=> SELECT '2011-01-01 00:00:00+03'::TIMESTAMP WITH TIME ZONE;
      timestamptz       
------------------------
 2011-01-01 06:00:00+09
(1 row)

foo=> SET TIMEZONE TO 'Australia/Melbourne';
SET
foo=> SELECT '2011-01-01 00:00:00'::TIMESTAMP;
      timestamp      
---------------------
 2011-01-01 00:00:00
(1 row)

foo=> SELECT '2011-01-01 00:00:00'::TIMESTAMP WITH TIME ZONE;
      timestamptz       
------------------------
 2011-01-01 00:00:00+11
(1 row)

foo=> SELECT '2011-01-01 00:00:00+03'::TIMESTAMP;
      timestamp      
---------------------
 2011-01-01 00:00:00
(1 row)

foo=> SELECT '2011-01-01 00:00:00+03'::TIMESTAMP WITH TIME ZONE;
      timestamptz       
------------------------
 2011-01-01 08:00:00+11
(1 row)

Staram się wyjaśnić to bardziej zrozumiale niż przywołana dokumentacja PostgreSQL.

Żaden TIMESTAMPwariant nie przechowuje strefy czasowej (ani przesunięcia), pomimo sugerowanych nazw. Różnica polega na interpretacji przechowywanych danych (i zamierzonej aplikacji), a nie na samym formacie przechowywania:

• TIMESTAMP WITHOUT TIME ZONEprzechowuje lokalną datę i godzinę (aka. kalendarz ścienny i zegar ścienny). Jego strefa czasowa jest nieokreślona, ​​o ile PostgreSQL może powiedzieć (chociaż twoja aplikacja może wiedzieć, co to jest). Dlatego PostgreSQL nie dokonuje konwersji związanej ze strefą czasową na wejściu lub wyjściu. Jeśli wartość została wprowadzona do bazy danych jako '2011-07-01 06:30:30', to bez względu na to, w której strefie czasowej wyświetlisz ją później, nadal będzie podawać rok 2011, miesiąc 07, dzień 01, 06 godzin, 30 minut i 30 sekund (w pewnym formacie). Ponadto, każdy przesunięcie strefy czasowej lub podać na wejściu jest ignorowany przez PostgreSQL, tak '2011-07-01 06:30:30+00'i '2011-07-01 06:30:30+05'są takie same jak po prostu '2011-07-01 06:30:30'. Dla programistów Java: jest to analogiczne do java.time.LocalDateTime.

• TIMESTAMP WITH TIME ZONEzapisuje punkt na linii czasu UTC. To, jak to wygląda (ile godzin, minut itp.) Zależy od twojej strefy czasowej, ale zawsze odnosi się do tej samej „fizycznej” chwili (jak moment rzeczywistego zdarzenia fizycznego). Dane wejściowe są wewnętrznie konwertowane na UTC i tak są przechowywane. W tym celu musi być znane przesunięcie wejścia, więc jeśli dane wejściowe nie zawierają wyraźnego przesunięcia lub strefy czasowej (jak '2011-07-01 06:30:30'), zakłada się, że znajduje się w bieżącej strefie czasowej sesji PostgreSQL, w przeciwnym razie zostanie użyte jawnie określone przesunięcie lub strefa czasowa (jak w '2011-07-01 06:30:30+05'). Dane wyjściowe są wyświetlane przekonwertowane na bieżącą strefę czasową sesji PostgreSQL. Dla programistów Java: Jest to analogiczne do java.time.Instant(z niższą rozdzielczością), ale w JDBC i JPA 2.2 należy go odwzorować java.time.OffsetDateTime( java.util.Datelubjava.sql.Timestamp oczywiście).

Niektórzy twierdzą, że obie TIMESTAMPwersje przechowują datę i godzinę UTC. W pewnym sensie, ale moim zdaniem jest to mylące. TIMESTAMP WITHOUT TIME ZONEjest przechowywany jak TIMESTAMP WITH TIME ZONE, który renderowany ze strefą czasową UTC daje ten sam rok, miesiąc, dzień, godziny, minuty, sekundy i mikrosekundy, jak w lokalnej dacie i czasie. Ale to nie ma oznaczać punktu na linii czasu, który mówi interpretacja UTC, to po prostu sposób, w jaki są zakodowane lokalne pola daty i godziny. (To jakaś grupa kropek na linii czasu, ponieważ strefa czasu rzeczywistego to nie UTC; nie wiemy, co to jest.)

Oto przykład, który powinien pomóc. Jeśli masz znacznik czasu ze strefą czasową, możesz przekonwertować go na dowolną inną strefę czasową. Jeśli nie masz podstawowej strefy czasowej, nie zostanie poprawnie przekonwertowana.

SELECT now(),
   now()::timestamp,
   now() AT TIME ZONE 'CST',
   now()::timestamp AT TIME ZONE 'CST'

Wynik:

-[ RECORD 1 ]---------------------------
now      | 2018-09-15 17:01:36.399357+03
now      | 2018-09-15 17:01:36.399357
timezone | 2018-09-15 08:01:36.399357
timezone | 2018-09-16 02:01:36.399357+03