Typ danych timestampto krótka nazwa timestamp without time zone.
Druga opcja timestamptzto skrót od timestamp with time zone.
timestamptzjest dosłownie preferowanym typem w rodzinie daty / godziny. Stało typispreferredsię w pg_type, które mogą być istotne:
Pamięć wewnętrzna i epoka
Wewnętrznie znaczniki czasu zajmują 8 bajtów pamięci na dysku iw pamięci RAM. Jest to liczba całkowita reprezentująca liczbę mikrosekund z epoki Postgresa, 2000-01-01 00:00:00 UTC.
Postgres ma również wbudowaną wiedzę na temat powszechnie używanego czasu UNIX zliczającego sekundy z epoki UNIX, 1970-01-01 00:00:00 UTC i używa go w funkcjach to_timestamp(double precision)lub EXTRACT(EPOCH FROM timestamptz).
Kod źródłowy:
* Znaczniki czasu, a także pola interwałów h / m / s są przechowywane jako pliki
* wartości int64 z jednostkami mikrosekund. (Kiedyś tak było
* podwójne wartości z jednostkami sekund.)
I:
/ * Odpowiedniki daty juliańskiej dnia 0 w obliczeniach Unix i Postgres * /
# zdefiniować UNIX_EPOCH_JDATE 2440588 / * == date2j (1970, 1, 1) * /
# zdefiniować POSTGRES_EPOCH_JDATE 2451545 / * == date2j (2000, 1, 1) * /
Rozdzielczość mikrosekundowa przekłada się na maksymalnie 6 cyfr ułamkowych na sekundy.
timestamp
Wartość wpisana jako mówi Postgresowi, że żadna strefa czasowa nie jest wyraźnie określona. Zakładana jest aktualna strefa czasowa. Postgres ignoruje dodany przez pomyłkę modyfikator strefy czasowej!timestamp [without time zone]
Do wyświetlania nie są przesuwane godziny. Przy tych samych ustawieniach strefy czasowej wszystko jest w porządku. W przypadku ustawienia innej strefy czasowej znaczenie zmienia się, ale wartość i wyświetlacz pozostają takie same.
timestamptz
Obsługa timestamp with time zonejest nieco inna. Cytuję instrukcję tutaj :
Ponieważ timestamp with time zonewartość przechowywana wewnętrznie jest zawsze w UTC (uniwersalny czas koordynowany ...)
Odważne podkreślenie moje. Sama strefa czasowa nigdy nie jest przechowywana . Jest to modyfikator wejściowy używany do obliczania odpowiedniego znacznika czasu UTC, który jest przechowywany - lub modyfikator wyjściowy używany do obliczania czasu lokalnego do wyświetlenia - z dołączonym przesunięciem strefy czasowej. Jeśli nie dodasz przesunięcia dla timestamptzwejścia, przyjmowane jest bieżące ustawienie strefy czasowej sesji. Wszystkie obliczenia są wykonywane z wartościami znacznika czasu UTC. Jeśli musisz (lub być może będziesz musiał) zajmować się więcej niż jedną strefą czasową, użyj timestamptz.
Klienci tacy jak psql lub pgAdmin lub dowolna aplikacja komunikująca się przez libpq (jak Ruby z pg gem) otrzymują znacznik czasu i przesunięcie dla bieżącej strefy czasowej lub zgodnie z żądaną strefą czasową (patrz poniżej). Jest to zawsze ten sam punkt w czasie , zmienia się tylko format wyświetlania. Lub, jak to ujmuje instrukcja :
Wszystkie daty i godziny uwzględniające strefę czasową są przechowywane wewnętrznie w formacie UTC. Są one konwertowane na czas lokalny w strefie określonej przez
parametr konfiguracyjny TimeZone , zanim zostaną wyświetlone klientowi.
Rozważmy ten prosty przykład (w psql):
db = # SELECT timestamptz '2012-03-05 20:00 +03 ';
timestamptz
------------------------
2012-03-05 18:00:00 +01
Odważne podkreślenie moje. Co tu się stało?
Wybrałem dowolne przesunięcie strefy czasowej +3dla literału wejściowego. Dla Postgresa jest to tylko jeden z wielu sposobów wprowadzania znacznika czasu UTC 2012-03-05 17:00:00. Wynik zapytania jest wyświetlany dla aktualnego ustawienia strefy czasowej Wiednia / Austrii w moim teście, który ma przesunięcie +1zimą i +2latem: 2012-03-05 18:00:00+01ponieważ przypada na czas zimowy.
Postgres już zapomniał, w jaki sposób wprowadzono tę wartość. Wszystko, co pamięta, to wartość i typ danych. Podobnie jak w przypadku liczby dziesiętnej. numeric '003.4', numeric '3.40'Lub numeric '+3.4'- wszystko wynik w dokładnie tej samej wartości wewnętrznej.
AT TIME ZONE
Gdy tylko opanujesz tę logikę, możesz zrobić, co chcesz. Jedyne, czego teraz brakuje, to narzędzie do interpretowania lub reprezentowania literałów sygnatury czasowej według określonej strefy czasowej. W tym miejscu AT TIME ZONEpojawia się konstrukcja. Istnieją dwa różne przypadki użycia. timestamptzjest konwertowany na timestampi 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 z:
SELECT timestamptz '2012-03-05 17:00:00 UTC'
Aby wyświetlić ten sam punkt w czasie co EST timestamp(wschodni czas standardowy):
SELECT timestamp '2012-03-05 17:00:00' AT TIME ZONE 'UTC' AT TIME ZONE 'EST'
Zgadza się, AT TIME ZONE 'UTC' dwa razy . Pierwsza interpretuje timestampwartość jako (podaną) sygnaturę czasową UTC zwracającą typ timestamptz. Drugi konwertuje timestamptzdo timestampw danej strefie czasowej „EST” - co zegar w czasach wyświetlaczy strefa EST w tym wyjątkowym momencie.
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 z datownikami zawierającymi ten sam znacznik czasu UTC 2012-03-05 17:00:00. Dziewiąty rząd działa w mojej strefie czasowej, ale jest złą pułapką. Zobacz poniżej.
① Wiersze 6–8 zawierające nazwę strefy czasowej i skrót strefy czasowej dla czasu hawajskiego podlegają czasowi letnim (DST) i mogą się różnić, chociaż obecnie nie. Nazwa strefy czasowej, taka jak, 'US/Hawaii'jest świadoma reguł czasu letniego i wszystkich historycznych przesunięć automatycznie, podczas gdy skrót, taki jak, HSTjest tylko głupim kodem dla stałego przesunięcia. Konieczne może być dodanie innego skrótu dla czasu letniego / standardowego. Nazwa poprawnie interpretuje dowolny znacznik czasu w danej strefie czasowej. Skrót jest tani, ale musi być odpowiednia dla danego znacznika czasu:
Czas letni nie należy do najwspanialszych pomysłów, jakie ludzkość kiedykolwiek wymyśliła.
② Wiersz 9, oznaczony jako załadowany, działa dla mnie , ale tylko przez przypadek. Jeśli jawnie rzutujesz literał na timestamp [without time zone], wszelkie przesunięcie strefy czasowej jest ignorowane ! Używany jest tylko sam znacznik czasu. Wartość jest następnie automatycznie przekształcana timestamptzw przykładzie w celu dopasowania do typu kolumny. Na tym etapie timezonezakłada się ustawienie bieżącej sesji, która +1w moim przypadku jest tą samą strefą czasową (Europa / Wiedeń). Ale prawdopodobnie nie w twoim przypadku - co spowoduje inną wartość. Krótko mówiąc: nie timestamptzprzesyłaj literałów do timestamplub tracisz przesunięcie strefy czasowej.
Twoje pytania
Użytkownik przechowuje czas, na przykład 17 marca 2012 r. O 19:00. Nie chcę, aby były zapisywane konwersje lub strefa czasowa.
Sama strefa czasowa nigdy nie jest przechowywana. Użyj jednej z powyższych metod, aby wprowadzić znacznik czasu UTC.
Używam tylko strefy czasowej określonej przez użytkownika, aby uzyskać rekordy „przed” lub „po” bieżącym czasie w lokalnej strefie czasowej użytkowników.
Możesz użyć jednego zapytania dla wszystkich klientów w różnych strefach czasowych.
Dla bezwzględnego czasu globalnego:
SELECT * FROM tbl WHERE time_col > (now() AT TIME ZONE 'UTC')::time
Czas według lokalnego zegara:
SELECT * FROM tbl WHERE time_col > now()::time
Nie masz jeszcze dość ogólnych informacji? W instrukcji jest więcej.