Como obtenho o carimbo de data / hora Unix atual do PostgreSQL?

No postgres, timestamp with time zone pode ser abreviado como timestamptz e timestamp without time zone como timestamp. Vou usar os nomes de tipo mais curtos para simplificar.

Obter o carimbo de data / hora Unix de um postgres timestamptz como now() é simples, como você diz, apenas:

select extract(epoch from now()); 

Isso é realmente tudo que você precisa saber sobre como obter o tempo absoluto de qualquer coisa do tipo timestamptz, incluindo now().

As coisas só ficam complicadas quando você tem um campo timestamp .

Quando você coloca timestamptz dados como now() nesse campo, eles serão primeiro convertidos para um fuso horário específico (explicitamente com at time zone ou convertendo para o fuso horário da sessão) e as informações de fuso horário são descartadas . Não se referem mais a um tempo absoluto. É por isso você normalmente não deseja armazenar carimbos de data / hora como timestamp e normalmente usaria timestamptz – talvez um filme seja lançado às 18h em um determinado data em cada fuso horário , esse é o tipo de caso de uso.

Se você trabalhar apenas em um único fuso horário, poderá se safar (mal) usando timestamp. A conversão de volta para timestamptz é inteligente o suficiente para lidar com o DST, e os carimbos de data / hora são considerados, para fins de conversão, no fuso horário atual. Aqui está um exemplo para 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

Mas, observe o seguinte 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

Isso é porque :

O PostgreSQL nunca examina o conteúdo de uma string literal antes de determinar seu tipo e, portanto, tratará ambos […] como carimbo de data / hora sem fuso horário. garanta que um literal seja tratado como carimbo de data / hora com fuso horário, forneça a ele o tipo explícito correto … Em um literal que foi determinado como carimbo de data / hora sem fuso horário, o PostgreSQL ignorará silenciosamente qualquer indicação de fuso horário

Comentários

• Qualquer ideia de como converter o decimal resultante em um inteiro sem vírgula (quero dizer, mesclar o número e o decimal como um grande inteiro). Obrigado.
• Como isso , mas eu ' tenho certeza de que você não ' realmente deseja fazer isso. Talvez você queira multiplicar por uma potência de dez e retirar todos os decimais restantes?
• @ W.M. Talvez assim? SELECT FLOOR(EXTRACT(epoch FROM NOW())*1000);

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

não retorna o carimbo de data / hora correto porque a conversão de fuso horário postgres descarta as informações de fuso horário do resultado:

9.9.3. NO FUSO HORÁRIO

Sintaxe: timestamp sem fuso horário AT TIME ZONE fuso
Retorna: timestamp com fuso horário
Tratar o timestamp sem fuso horário como localizado no fuso horário especificado

Sintaxe: carimbo de data / hora com fuso horário AT TIME ZONE fuso
Retorna: carimbo de data / hora sem fuso horário
Converte o carimbo de hora fornecido com fuso horário para o novo fuso horário, sem designação de fuso horário

depois, o extract olha para o timestamp sem fuso horário e considera-o como a hora local (embora já seja utc de facto).

A forma correta seria:

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) 

Na última linha, o primeiro at time zone realiza a conversão, o segundo e um atribui um novo fuso horário ao resultado.