Início / dba / Perguntas / 2796
Accepted
Jonas
Asked: 2011-05-19 00:27:10 +0800 CST

Como obtenho o timestamp unix atual do PostgreSQL?

  • 772

O carimbo de data/hora do Unix é o número de segundos desde a meia-noite UTC de 1º de janeiro de 1970.

Como obtenho o timestamp unix correto do PostgreSQL?

Ao comparar com currenttimestamp.com e timestamp.1e5b.de , não recebo o tempo esperado do PostgreSQL:

Isso retorna o timestamp correto:

SELECT extract(epoch from now());

Enquanto isso não:

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

Eu moro no fuso horário UTC +02. Qual é a maneira correta de obter o timestamp unix atual do PostgreSQL?

Isso retorna a hora e o fuso horário corretos:

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

Outra comparação:

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
postgresql timestamp

2 respostas

  1. Best Answer

    No postgres, timestamp with time zonepode ser abreviado como timestamptz, e timestamp without time zonecomo timestamp. Usarei os nomes de tipo mais curtos para simplificar.

    Obter o timestamp Unix de um postgres timestamptzcomo now()é simples, como você diz, apenas:

    select extract(epoch from now());

    Isso é realmente tudo o 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 timestampcampo.

    Quando você coloca timestamptzdados como now()nesse campo, primeiro eles são convertidos para um fuso horário específico (explicitamente com at time zoneou convertendo para o fuso horário da sessão) e as informações do fuso horário são descartadas . Já não se refere a um tempo absoluto. É por isso que você geralmente não deseja armazenar carimbos de data e hora como timestampe normalmente usaria timestamptz- talvez um filme seja lançado às 18h em uma data específica 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 (mau) usando timestamp. A conversão de volta para timestamptzé inteligente o suficiente para lidar com o horário de verão, e os timestamps 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

    Isto porque :

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

    • 125 
  2. 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 zone
    Retorna: timestamp com fuso horário
    Tratar dado timestamp sem fuso horário como localizado no fuso horário especificado

    Sintaxe: timestamp com fuso horário AT TIME ZONE zone
    Retorna: timestamp sem fuso horário
    Converte o timestamp dado com fuso horário para o novo fuso horário, sem designação de fuso horário

    depois, extract examina o timestamp sem fuso horário e considera que é uma hora local (embora já seja utc de fato).

    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 zonerealiza a conversão, o segundo atribui um novo fuso horário ao resultado.

    • 32

relate perguntas