Ignorowanie stref czasowych w Rails i PostgreSQL

Typ danych timestamptz to krótka nazwa dla timestamp with time zone.
Druga opcja timestamp jest skrótem od timestamp without time zone.

timestamptz jest typem preferowanym w rodzinie daty/czasu, dosłownie. Ma typispreferred ustawione w pg_type, które mogą być istotne:

• generowanie szeregów czasowych pomiędzy dwoma datami w PostgreSQL

Pamięć wewnętrzna i Epoka

Wewnętrznie znaczniki czasu zajmują 8 bajtów pamięci na dysku i w pamięci RAM. Jest to wartość całkowita reprezentująca liczbę mikrosekund z epoki Postgres, 2000-01-01 00: 00: 00 UTC.

Postgres posiada również wbudowaną wiedzę na temat powszechnie używanego czasu Uniksa licząc sekundy z epoki Uniksa, 1970-01-01 00: 00: 00 UTC i używa tego w funkcjach to_timestamp(double precision) lub EXTRACT(EPOCH FROM timestamptz).

Źródło kod:

* Timestamps, as well as the h/m/s fields of intervals, are stored as
* int64 values with units of microseconds.  (Once upon a time they were  
* double values with units of seconds.)

I:

/* Julian-date equivalents of Day 0 in Unix and Postgres reckoning */  
#define UNIX_EPOCH_JDATE        2440588 /* == date2j(1970, 1, 1) */  
#define POSTGRES_EPOCH_JDATE    2451545 /* == date2j(2000, 1, 1) */  

Rozdzielczość mikrosekund przekłada się na maksymalnie 6 cyfr ułamkowych przez sekundy.

timestamp

Wartość wpisana jako timestamp informuje Postgres, że nie podano strefy czasowej. Przyjmuje się aktualną strefę czasową. Postgres ignoruje modyfikator strefy czasowej dodany do literała wejściowego przez pomyłkę!

Nie przesuwa się godzin wyświetlania. Przy tym samym ustawieniu strefy czasowej wszystko jest w porządku. Dla innego Strefa czasowa ustawia znaczenie zmienia się, ale wartość i wyświetlacz pozostają takie same.

timestamptz

Postępowanie z timestamptz jest subtelnie Inna. cytuję tutaj instrukcję :

Dla timestamp with time zone, wewnętrznie przechowywana wartość to zawsze w UTC (Uniwersalny czas koordynowany ...)

/ Align = "left" / Strefa czasowa nigdy nie jest przechowywana . Jest to modyfikator wejściowy używany do obliczania według Znacznik czasu UTC, który jest przechowywany - lub i modyfikator wyjścia używany do obliczania czasu lokalnego do wyświetlenia-z dołączonym przesunięciem strefy czasowej. Jeśli nie dodasz offsetu dla timestamptz na wejściu, zostanie przyjęte ustawienie bieżącej strefy czasowej sesji. Wszystkie obliczenia są wykonywane z wartościami znacznika czasu UTC. Jeśli musisz (lub może musisz) mieć do czynienia z więcej niż jedną strefą czasową, użyj timestamptz. Innymi słowy: jeśli istnieją jakiekolwiek wątpliwości lub nieporozumienia co do zakładanej strefy czasowej, wybierz timestamptz. Stosuje się w większości zastosowań sprawy.

Klienci tacy jak psql lub pgAdmin lub dowolna aplikacja komunikująca się przez libpq (jak Ruby z PG gem) są prezentowani z znacznikiem czasu plus offset dla bieżącej strefy czasowej lub zgodnie z żądaną strefą czasową (patrz poniżej). Jest to zawsze ten sam punkt w czasie , tylko format wyświetlania jest różny. Lub, jak podaje Instrukcja :

Wszystkie daty i godziny związane ze strefą czasową są przechowywane wewnętrznie w UTC. Oni są przeliczany na czas lokalny w strefie określonej przez TimeZone parametr konfiguracyjny przed wyświetlaniem klientowi.

Przykład (w psql):

db=# SELECT timestamptz '2012-03-05 20:00+03';
      timestamptz
------------------------
 2012-03-05 18:00:00+01

Co tu się stało?
Wybrałem dowolne przesunięcie strefy czasowej +3 dla literału wejściowego. W Postgres jest to tylko jeden z wielu sposobów wprowadzania znacznika czasu UTC 2012-03-05 17:00:00. Wynikiem zapytania jest wyświetlone dla bieżącego ustawienia strefy czasowej Wiedeń / Austria w moim teście, który ma przesunięcie +1 w okresie zimowym i +2 w okresie letnim ("czas letni", DST). Więc [31]} jako DST dopiero później.

Postgres już zapomniał jak ta wartość została wprowadzona. Zapamiętuje tylko wartość typu danych. Tak jak z liczbą dziesiętną. numeric '003.4', numeric '3.40' or numeric '+3.4' - wszystkie dają dokładnie tę samą wartość wewnętrzną.

AT TIME ZONE

Jak tylko opanujesz logikę, możesz robić, co chcesz. Wszystko, czego teraz brakuje, to narzędzie do interpretuje lub reprezentuje literały znacznika czasu zgodnie z określoną strefą czasową. Tam AT TIME ZONE / align = "left" / Istnieją dwa różne przypadki użycia. timestamptz jest zamieniana na timestamp i odwrotnie.

Aby wprowadzić UTC timestamptz 2012-03-05 17:00:00+0:

SELECT timestamp '2012-03-05 17:00:00' AT TIME ZONE 'UTC'

... co jest równoważne:

SELECT timestamptz '2012-03-05 17:00:00 UTC'

Aby wyświetlić ten sam punkt w czasie co EST timestamp (Eastern Standard Time):

SELECT timestamp '2012-03-05 17:00:00' AT TIME ZONE 'UTC' AT TIME ZONE 'EST'

That ' s right, AT TIME ZONE 'UTC' dwa razy . Pierwsza interpretuje timestamp wartość jako (podana) znacznik czasu UTC zwracający Typ timestamptz. Drugi zamienia timestamptz na timestamp W podanej strefie czasowej 'EST' - co zegar w strefie czasowej EST wyświetla w tym unikalnym punkcie czasu.

Przykłady

SELECT ts AT TIME ZONE 'UTC'
FROM  (
   VALUES
      (1, timestamptz '2012-03-05 17:00:00+0')
    , (2, timestamptz '2012-03-05 18:00:00+1')
    , (3, timestamptz '2012-03-05 17:00:00 UTC')
    , (4, timestamp   '2012-03-05 11:00:00'  AT TIME ZONE '+6') 
    , (5, timestamp   '2012-03-05 17:00:00'  AT TIME ZONE 'UTC') 
    , (6, timestamp   '2012-03-05 07:00:00'  AT TIME ZONE 'US/Hawaii')  -- ①
    , (7, timestamptz '2012-03-05 07:00:00 US/Hawaii')                  -- ①
    , (8, timestamp   '2012-03-05 07:00:00'  AT TIME ZONE 'HST')        -- ①
    , (9, timestamp   '2012-03-05 18:00:00+1')  -- ② loaded footgun!
      ) t(id, ts);

Zwraca 8 (lub 9) identycznych wierszy z kolumnami znacznika czasowego trzymającymi ten sam znacznik czasu UTC 2012-03-05 17:00:00. Dziewiąty rząd działa w mojej strefie czasowej, ale to zła pułapka. Patrz poniżej.

① wiersze 6 - 8 ze strefą czasową nazwa i Strefa czasowaSkrót dla Hawajów czasu podlegają DST (czas letni) i mogą się różnić, choć nie obecnie. Nazwa strefy czasowej jak {[48] } jest świadoma reguł DST i wszystkich historycznych przesunięć automatycznie, podczas gdy skrót jak HST jest tylko głupi kod dla stałego przesunięcia. Może być konieczne dodanie innego skrótu dla czasu letniego / standardowego. nazwa poprawnie interpretuje dowolny znacznik czasu w podanym Strefa czasowa. Nie jest to jednak żaden problem, ponieważ nie jest to możliwe.]}

• nazwy stref czasowych o identycznych właściwościach dają inny wynik, gdy zostaną zastosowane do znacznika czasu

_{czas letni nie należy do najjaśniejszych pomysłów, jakie kiedykolwiek wymyśliła ludzkość.}

Row wiersz 9, oznaczony jako loaded footgun działa dla mnie, ale tylko przez przypadek. Jeśli wyraźnie rzucisz literal do timestamp [without time zone], przesunięcie strefy czasowej jest ignorowane ! Używany jest tylko bare timestamp. Wartość jest następnie automatycznie wymuszana na timestamptz w przykładzie, aby dopasować typ kolumny. W tym kroku zakłada się ustawienie timezone bieżącej sesji, która w moim przypadku jest tą samą strefą czasową +1 (Europa / Wiedeń). Ale prawdopodobnie nie w Twoim przypadku - co spowoduje inną wartość. W skrócie: nie rzucaj liter timestamptz na timestamp, bo stracisz przesunięcie strefy czasowej.

Twój pytania

Użytkownik przechowuje czas, powiedzmy 17 marca 2012, 19: 00. Nie chcę strefy czasowej konwersji lub strefy czasowej, która ma być przechowywana.

Sama strefa czasowa nigdy nie jest przechowywana. Użyj jednej z powyższych metod, aby wprowadzić znacznik czasu UTC.

Używam tylko określonej przez użytkowników strefy czasowej, aby uzyskać rekordy "przed" lub 'after' bieżący czas w lokalnej strefie czasowej users.

Możesz użyć jednego zapytania dla wszystkich klientów w różnych strefach czasowych.
Dla absolutnych czas globalny:

SELECT * FROM tbl WHERE time_col > (now() AT TIME ZONE 'UTC')::time

Dla czasu według lokalnego zegara:

SELECT * FROM tbl WHERE time_col > now()::time

Nie znudziły Ci się jeszcze podstawowe informacje? jest więcej w instrukcji.