Cum obțin marcajul de timp actual Unix de la PostgreSQL?

În postgres, timestamp with time zone poate fi abreviat ca timestamptz și timestamp without time zone ca timestamp. Voi folosi numele de tip mai scurte pentru simplitate.

Obținerea marcajului de timp Unix de la un postgres timestamptz ca now() este simplu, după cum spuneți, doar:

select extract(epoch from now()); 

Asta este tot ce trebuie să știți despre obținerea timpului absolut din orice tip de tip timestamptz, inclusiv now().

Lucrurile se complică numai atunci când aveți un câmp timestamp .

Când puneți timestamptz date precum now() în acel câmp, acesta va fi mai întâi convertit într-un anumit fus orar (fie în mod explicit cu at time zone, fie prin conversia la fusul orar al sesiunii) și informațiile despre fusul orar sunt eliminate . Nu se mai referă la un timp absolut. De aceea de obicei nu doriți să stocați marcaje temporale ca timestamp și în mod normal utilizați timestamptz – poate că un film va fi lansat la 18:00 pe un anumit data în fiecare fus orar , acesta este tipul de caz de utilizare.

Dacă lucrați vreodată într-un singur fus orar, ați putea scăpa de (greșit) folosind timestamp. Conversia înapoi la timestamptz este suficient de inteligentă pentru a face față orei de vară, iar marcajele temporale sunt presupuse, în scopul conversiei, ca fiind în fusul orar actual. Iată un exemplu pentru 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

Dar, rețineți următorul comportament confuz:

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

Acest se datorează faptului că :

PostgreSQL nu examinează niciodată conținutul unui șir literal înainte de a-i determina tipul și, prin urmare, va trata pe amândouă […] ca marcaj de timp fără fus orar. asigurați-vă că un literal este tratat ca marcaj de timp cu fusul orar, dați-i tipul explicit corect … Într-un literal care a fost stabilit ca fiind marcaj de timp fără fus orar, PostgreSQL va ignora în tăcere orice indicație de fus orar

Comentarii

• Orice idee despre cum se poate converti zecimalul rezultat într-un număr întreg fără punct zecimal (mă refer la îmbinarea numărului cu zecimalul ca un întreg mare). Mulțumesc.
• Ca aceasta , dar ' sunt sigur că nu ' chiar vrei să faci asta. Poate doriți să vă înmulțiți cu o putere de zece și să eliminați orice zecimal rămas?
• @ W.M. Poate așa? SELECT FLOOR(EXTRACT(epoch FROM NOW())*1000);

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

nu returnează marcajul de timp corect deoarece conversia fusului orar postgres aruncă informațiile privind fusul orar din rezultat:

9.9.3. ÎN FUS ORAR

Sintaxă: marcaj de timp fără fus orar ÎN ZONA FUS ORAR
Returnează: marcaj de timp cu fus orar
Tratați marcajul de timp dat fără fus orar așa cum se află în fusul orar specificat

Sintaxă: timestamp cu fus orar ÎN ZONA FUSUL ORAR
Returnează: marca temporală fără fus orar
Convertește marca de timp dată cu fus orar în noul fus orar, fără desemnarea fusului orar

după aceea, extractul analizează marcajul de timp fără fus orar și consideră că este o oră locală (deși este deja utc de fapt).

Modul corect ar fi:

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) 

În ultima linie primul at time zone efectuează conversia, seco nd unul atribuie noul fus orar rezultatului.