Jak uzyskać bieżący znacznik czasu unix z PostgreSQL?

Uniksowy znacznik czasu to liczba sekund od północy UTC 1 stycznia 1970 r.

Jak uzyskać prawidłowy znacznik czasu unix z PostgreSQL?

Porównując do currenttimestamp.com i timestamp.1e5b.de , nie otrzymuję oczekiwanego czasu z PostgreSQL:

Zwraca poprawny znacznik czasu:

SELECT extract(epoch from now());

Chociaż to nie:

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

Mieszkam w strefie czasowej UTC +02. Jaki jest prawidłowy sposób uzyskania bieżącego znacznika czasu unix z PostgreSQL?

Zwraca prawidłową godzinę i strefę czasową:

SELECT now();
              now
-------------------------------
 2011-05-18 10:34:10.820464+02

Kolejne porównanie:

select now(), 
extract(epoch from now()), 
extract(epoch from now() at time zone 'utc');
              now              |    date_part     |    date_part
-------------------------------+------------------+------------------
 2011-05-18 10:38:16.439332+02 | 1305707896.43933 | 1305700696.43933
(1 row)

Unix timestamp from the web sites:
1305707967

W postgresie timestamp with time zonemożna go skrócić jako timestamptzi timestamp without time zonejako timestamp. Dla uproszczenia użyję krótszych nazw typów.

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

select extract(epoch from now());

To naprawdę wszystko, co musisz wiedzieć o uzyskiwaniu czasu bezwzględnego z dowolnego rodzaju timestamptz, w tym now().

Sprawy komplikują się tylko wtedy, gdy masz timestamppole.

Kiedy umieścisz timestamptzdane jak now()w tym polu, najpierw zostaną one przekonwertowane na określoną strefę czasową (jawnie z at time zonelub poprzez konwersję do strefy czasowej sesji), a informacja o strefie czasowej zostanie odrzucona . Nie odnosi się już do czasu absolutnego. Dlatego zwykle nie chcesz przechowywać znaczników czasu, timestampktórych normalnie używasz timestamptz- być może film zostanie wypuszczony o 18:00 w określonym dniu w każdej strefie czasowej , jest to przypadek użycia.

Jeśli kiedykolwiek pracujesz tylko w jednej strefie czasowej, możesz uniknąć (niewłaściwego) używania timestamp. Konwersja z powrotem do timestamptzjest 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

Jest tak, ponieważ :

PostgreSQL nigdy nie bada zawartości ciągu literalnego przed określeniem jego typu, dlatego też traktuje zarówno […] jak znacznik czasu bez strefy czasowej. Aby mieć pewność, że literał jest traktowany jako znacznik czasu ze strefą czasową, nadaj mu poprawny jawny typ… W literale, który został określony jako znacznik czasu bez strefy czasowej, PostgreSQL po cichu zignoruje wszelkie wskazania strefy czasowej

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

nie zwraca poprawnego znacznika czasu, ponieważ konwersja strefy czasowej postgres odrzuca informacje o strefie czasowej z wyniku:

9.9.3. W STREFIE CZASU

Składnia: znacznik czasu bez strefy czasowej Strefa AT TIME ZONE
Zwraca: znacznik czasu ze strefą czasową
Traktuj podany znacznik czasu bez strefy czasowej, jak znajduje się w określonej strefie czasowej

Składnia: znacznik czasu ze strefą czasową Strefa czasowa AT TIME
Zwraca: znacznik czasu bez strefy czasowej
Konwertuj podany znacznik czasu ze strefą czasową na nową strefę czasową, bez oznaczenia strefy czasowej

następnie wyodrębnia patrzy na znacznik czasu bez strefy czasowej i uznaje go za czas lokalny (chociaż tak naprawdę jest to utc).

Prawidłowy sposób to:

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 ostatniej linii pierwsza at time zonewykonuje konwersję, druga przypisuje nową strefę czasową do wyniku.