Jak uzyskać aktualny znacznik czasu unixa z PostgreSQL?

W postgres, timestamp with time zone można skrócić jako timestamptz, a timestamp without time zone jako timestamp. Dla uproszczenia użyję krótszych nazw typów.

Pobieranie uniksowego znacznika czasu z postgres timestamptz jak now() jest proste, jak mówisz, po prostu:

select extract(epoch from now()); 

To naprawdę wszystko, co musisz wiedzieć, aby uzyskać absolutny czas z czegokolwiek typu timestamptz, w tym now().

Sprawy komplikują się tylko wtedy, gdy masz pole timestamp .

Kiedy umieścisz timestamptz dane, takie jak now() w tym polu, zostaną one najpierw przekonwertowane na określoną strefę czasową (jawnie za pomocą at time zone lub przez konwersję na strefę czasową sesji), a informacje o strefie czasowej są odrzucane . Nie odnosi się już do czasu bezwzględnego. Dlatego zwykle nie chcesz przechowywać sygnatur czasowych jako timestamp i normalnie używasz timestamptz – być może film zostanie wydany o 18:00 w określonym data w każdej strefie czasowej , to jest rodzaj przypadku użycia.

Jeśli pracujesz tylko w jednej strefie czasowej, możesz uciec z (niewłaściwym) użyciem timestamp. Konwersja z powrotem do timestamptz jest wystarczająco sprytna, aby poradzić sobie z czasem letnim, a do celów konwersji zakłada się, że znaczniki czasu znajdują się w bieżącej strefie czasowej. Oto przykład dla GMT / BST:

select "2011-03-27 00:59:00.0+00"::timestamptz::timestamp::timestamptz , "2011-03-27 01:00:00.0+00"::timestamptz::timestamp::timestamptz; /* |timestamptz |timestamptz | |:---------------------|:---------------------| |2011-03-27 00:59:00+00|2011-03-27 02:00:00+01| */ 

_DBFiddle

Zwróć jednak uwagę na następujące mylące zachowanie:

set timezone to 0; values(1, "1970-01-01 00:00:00+00"::timestamp::timestamptz) , (2, "1970-01-01 00:00:00+02"::timestamp::timestamptz); /* |column1|column2 | |------:|:---------------------| | 1|1970-01-01 00:00:00+00| | 2|1970-01-01 00:00:00+00| */ 

_DBFiddle

To jest spowodowane tym, że :

PostgreSQL nigdy nie sprawdza zawartości literału przed określeniem jego typu, dlatego traktuje oba […] jako znacznik czasu bez strefy czasowej. Aby upewnij się, że literał jest traktowany jako znacznik czasu ze strefą czasową, nadaj mu poprawny jawny typ… W przypadku literału, który został określony jako znacznik czasu bez strefy czasowej, PostgreSQL po cichu zignoruje wszelkie wskazania strefy czasowej

Komentarze

• Każdy pomysł, jak zamienić wynikowy numer dziesiętny na liczbę całkowitą bez separatora dziesiętnego (mam na myśli połączenie liczby i dziesiętnej jako jedna duża liczba całkowita). Dzięki.
• Na przykład to , ale ' na pewno nie ' naprawdę nie chcę tego robić. Może chcesz pomnożyć przez potęgę dziesięciu i usunąć pozostałe cyfry po przecinku?
• @ W.M. Może tak? SELECT FLOOR(EXTRACT(epoch FROM NOW())*1000);

SELECT extract(epoch from now() at time zone "utc"); 

nie zwraca poprawnej sygnatury czasowej, ponieważ konwersja strefy czasowej postgres odrzuca informacje o strefie czasowej z wyniku:

9.9.3. W STREFIE CZASOWEJ

Składnia: znacznik czasu bez strefy czasowej AT TIME ZONE zone
Zwraca: timestamp ze strefą czasową
Traktuj dany znacznik czasu bez strefy czasowej jako znajdujący się w określonej strefie czasowej

Składnia: znacznik czasu ze strefą czasową AT TIME ZONE zone
Zwraca: timestamp bez strefy czasowej
Konwertuj podany znacznik czasu ze strefą czasową na nową strefę czasową bez oznaczenia strefy czasowej

następnie extract sprawdza datownik bez strefy czasowej i traktuje go jako czas lokalny (chociaż w rzeczywistości jest to już utc).

Poprawny sposób wyglądałoby następująco:

select now(), extract(epoch from now()), -- correct extract(epoch from now() at time zone "utc"), -- incorrect extract(epoch from now() at time zone "utc" at time zone "utc"); -- correct now | date_part | date_part | date_part -------------------------------+------------------+------------------+------------------ 2014-10-14 10:19:23.726908+02 | 1413274763.72691 | 1413267563.72691 | 1413274763.72691 (1 row) 

W ostatnim wierszu pierwszy at time zone przeprowadza konwersję, druga jedna przypisuje wynikowi nową strefę czasową.