Как получить текущую временную метку unix из PostgreSQL?

Unix timestamp - это количество секунд с полуночи UTC 1 января 1970 года.

Как получить правильную временную метку unix из PostgreSQL?

По сравнению с currenttimestamp.com и меткой времени. 1e5b.de Я не получаю ожидаемое время от PostgreSQL:

Это возвращает правильную метку времени:

SELECT extract(epoch from now());

Пока это не так:

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

Я живу в часовом поясе UTC +02. Каков правильный способ получить текущую временную метку unix из PostgreSQL?

Это возвращает правильное время и часовой пояс:

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

Другое сравнение:

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
69 голосов | спросил Jonas 18 Maypm11 2011, 12:27:10

2 ответа


63

В postgres метка времени timestamp with time zone может быть сокращена как timestamptz и timestamp without time zone как timestamp. Я буду использовать более короткие имена типов для простоты.

Получение временной метки Unix из postgres timestamptz, например now(), просто, как вы говорите, просто:

select extract(epoch from now());

Это действительно все, что вам нужно знать о получении абсолютного времени от чего-либо типа timestamptz, включая now().

Все становится сложнее, если у вас есть поле timestamp.

Когда вы помещаете в это поле timestamptz данные, такие как now(), сначала преобразуются в определенный часовой пояс (либо явно с помощью at time zone или путем преобразования в часовой пояс сеанса), а информация о часовом поясе отбрасывается . Это больше не относится к абсолютному времени. Вот почему вы обычно не хотите хранить временные метки как timestamp и обычно использовали бы timestamptz), возможно, фильм будет выпущен в 18:00 в конкретную дату в каждом часовом поясе , это вариант использования.

Если вы работаете только в одном часовом поясе, вы можете уйти с (неправильным) с помощью timestamp. Преобразование обратно в timestamptz достаточно умное, чтобы справиться с DST, и временные метки предполагаются для целей конверсии в текущем часовом поясе. Вот пример для 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

Но обратите внимание на следующее запутанное поведение:

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

Этот объясняется тем, что :

  

PostgreSQL никогда не проверяет содержимое литеральной строки перед определением ее типа и поэтому будет относить обе [â | |] как временную метку без часового пояса. Чтобы гарантировать, что литерал рассматривается как временная метка с часовым поясом, дайте ему правильный явный тип ... В литерале, который был определен как временная метка без часового пояса, PostgreSQL молча игнорирует индикацию часового пояса

ответил Jack Douglas 18 Maypm11 2011, 13:21:38
17
SELECT extract(epoch from now() at time zone 'utc');

не возвращает правильную метку времени, потому что изменение часового пояса postgres выбрасывает информацию о часовом поясе из результата:

  

9.9.3. AT TIME ZONE

     

Синтаксис: временная метка без часового пояса Зона AT TIME ZONE
  Возвращает: временная метка с часовым поясом
  Обработать данную отметку времени без часовой пояс, как указано в указанном часовом поясе

     

Синтаксис: временная метка с часовым поясом зоны AT TIME ZONE
  Возвращает: временная метка без часового пояса
  Преобразовать заданную метку времени с часовым поясом в новый часовой пояс без указания часового пояса

после этого, извлечение просматривает временную метку без часового пояса и считает, что это локальное время (хотя на самом деле это уже utc).

Правильный способ:

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)

В последней строке первый at time zone выполняет преобразование, второй назначает новый часовой пояс для результата.

ответил axil 14 +04002014-10-14T12:46:31+04:00312014bEurope/MoscowTue, 14 Oct 2014 12:46:31 +0400 2014, 12:46:31

Похожие вопросы

Популярные теги

security × 330linux × 316macos × 2827 × 268performance × 244command-line × 241sql-server × 235joomla-3.x × 222java × 189c++ × 186windows × 180cisco × 168bash × 158c# × 142gmail × 139arduino-uno × 139javascript × 134ssh × 133seo × 132mysql × 132