¿Cómo obtengo la marca de tiempo actual de Unix de PostgreSQL?

En postgres, timestamp with time zone se puede abreviar como timestamptz y timestamp without time zone como timestamp. Usaré los nombres de tipos más cortos para simplificar.

Obtener la marca de tiempo de Unix de un postgres timestamptz como now() es simple, como usted dice, solo:

select extract(epoch from now()); 

Eso es realmente todo lo que necesita saber para obtener el tiempo absoluto de cualquier tipo de timestamptz, incluido now().

Las cosas solo se complican cuando tienes un campo timestamp .

Cuando pones timestamptz datos como now() en ese campo, primero se convertirán a una zona horaria en particular. (ya sea explícitamente con at time zone o mediante la conversión a la zona horaria de la sesión) y la información de la zona horaria se descarta . Ya no se refiere a una hora absoluta. Por eso normalmente no desea almacenar marcas de tiempo como timestamp y normalmente usaría timestamptz; tal vez una película se estrene a las 6:00 p. m. en un determinado fecha en cada zona horaria , ese es el tipo de caso de uso.

Si solo trabaja en una única zona horaria, podría salirse con la suya (mal) usando timestamp. La conversión de nuevo a timestamptz es lo suficientemente inteligente como para hacer frente al horario de verano, y se supone que las marcas de tiempo, para fines de conversión, se encuentran en la zona horaria actual. Aquí hay un ejemplo de 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

Pero, observe el siguiente comportamiento 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

Este se debe a que :

PostgreSQL nunca examina el contenido de una cadena literal antes de determinar su tipo y, por lo tanto, tratará tanto […] como una marca de tiempo sin zona horaria. asegúrese de que un literal se trate como marca de tiempo con zona horaria, asígnele el tipo explícito correcto … En un literal que se ha determinado que es marca de tiempo sin zona horaria, PostgreSQL ignorará silenciosamente cualquier indicación de zona horaria

Comentarios

• Alguna idea de cómo convertir el decimal resultante en un entero sin punto decimal (me refiero a fusionar el número y el decimal como un entero grande). Gracias.
• Me gusta esto , pero ' estoy seguro de que no ' realmente quieres hacer eso. ¿Quizás quieras multiplicar por una potencia de diez y quitar los decimales restantes?
• @ W.M. ¿Quizás así? SELECT FLOOR(EXTRACT(epoch FROM NOW())*1000);

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

no devuelve la marca de tiempo correcta porque la conversión de zona horaria de Postgres descarta la información de zona horaria del resultado:

9.9.3. EN LA ZONA HORARIA

Sintaxis: marca de tiempo sin zona horaria AT TIME ZONE zone
Devuelve: marca de tiempo con zona horaria
Trate la marca de tiempo dada sin zona horaria como ubicada en la zona horaria especificada

Sintaxis: marca de tiempo con zona horaria AT TIME ZONE zone
Devuelve: marca de tiempo sin zona horaria
Convierte la marca de tiempo dada con zona horaria a la nueva zona horaria, sin designación de zona horaria

luego, extract busca la marca de tiempo sin zona horaria y considera que es una hora local (aunque de hecho ya es utc).

La forma correcta sería:

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) 

En la última línea, el primer at time zone realiza la conversión, el seco Uno asigna una nueva zona horaria al resultado.