Come ottengo il timestamp unix corrente da PostgreSQL?

In postgres, timestamp with time zone può essere abbreviato come timestamptz e timestamp without time zone come timestamp. Userò nomi di tipo più brevi per semplicità.

Ottenere il timestamp di Unix da un timestamptz come now() è semplice, come dici tu, semplicemente:

select extract(epoch from now()); 

Questo è tutto ciò che devi sapere per ottenere il tempo assoluto da qualsiasi cosa di tipo timestamptz, incluso now().

Le cose si complicano solo quando hai un campo timestamp .

Quando inserisci timestamptz dati come now() in quel campo, verranno prima convertiti in un particolare fuso orario (esplicitamente con at time zone o convertendolo nel fuso orario della sessione) e le informazioni sul fuso orario vengono eliminate . Non si riferiscono più a unora assoluta. Questo è il motivo per cui di solito non vuoi memorizzare i timestamp come timestamp e normalmente utilizzeresti timestamptz – forse un film esce alle 18:00 su un determinato data in ogni fuso orario , questo è il tipo di caso duso.

Se lavori solo in un unico fuso orario potresti farla franca (male) usando timestamp. La riconversione a timestamptz è abbastanza intelligente da far fronte allora legale e, ai fini della conversione, si presume che i timestamp siano nel fuso orario corrente. Ecco un esempio per 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

Tuttavia, tieni presente il seguente comportamento confuso:

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

Questo è perché :

PostgreSQL non esamina mai il contenuto di una stringa letterale prima di determinarne il tipo, e quindi tratterà entrambi […] come timestamp senza fuso orario. assicurati che un letterale sia trattato come timestamp con fuso orario, dagli il tipo esplicito corretto … In un letterale che è stato determinato come timestamp senza fuso orario, PostgreSQL ignorerà silenziosamente qualsiasi indicazione di fuso orario

Commenti

• Qualche idea su come convertire il decimale risultante in un intero senza punto decimale (intendo unire il numero e il decimale come un intero grande). Grazie.
• Come questo ma ' sono sicuro che ' non lo desideri veramente. Forse vuoi moltiplicare per una potenza di dieci e togliere i decimali rimanenti?
• @ W.M. Forse così? SELECT FLOOR(EXTRACT(epoch FROM NOW())*1000);

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

non restituisce il timestamp corretto perché la conversione del fuso orario postgres elimina le informazioni sul fuso orario dal risultato:

9.9.3. AL FUSO ORARIO

Sintassi: timestamp senza fuso orario AT TIME ZONE zone
Restituisce: timestamp con fuso orario
Tratta data timestamp senza fuso orario come situato nel fuso orario specificato

Sintassi: timestamp con fuso orario AT TIME ZONE zone
Restituisce: timestamp senza fuso orario
Converte data timestamp con fuso orario nel nuovo fuso orario, senza designazione di fuso orario

in seguito, lestrazione guarda il timestamp senza fuso orario e considera lora locale (sebbene sia già utc in effetti).

Il modo corretto sarebbe:

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) 

Nellultima riga il primo at time zone esegue la conversione, il seco nd si assegna un nuovo fuso orario al risultato.