主页 / dba / 问题 / 2796
Accepted
Jonas
Asked: 2011-05-19 00:27:10 +0800 CST

如何从 PostgreSQL 获取当前的 unix 时间戳？

  • 772

Unix 时间戳是自 1970 年 1 月 1 日午夜 UTC 以来的秒数。

如何从 PostgreSQL 获取正确的 unix 时间戳？

与currenttimestamp.com和timestamp.1e5b.de进行比较时，我没有从 PostgreSQL 获得预期时间：

这将返回正确的时间戳：

SELECT extract(epoch from now());

虽然这不是：

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

我住在 UTC +02 时区。从 PostgreSQL 获取当前 unix 时间戳的正确方法是什么？

这将返回正确的时间和时区：

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

2 个回答

  1. Best Answer

    在 postgres 中，timestamp with time zone可以缩写为timestamptz, 和timestamp without time zoneas timestamp。为简单起见，我将使用较短的类型名称。

    timestamptz正如您所说，从 postgres 获取Unix时间戳now()很简单，只需：

    select extract(epoch from now());

    这就是你需要知道的关于从任何类型中获取绝对时间的全部信息timestamptz，包括now().

    timestamp只有当你有一个领域时，事情才会变得复杂。

    当您将timestamptz类似数据now()放入该字段时，它将首先转换为特定时区（显式使用at time zone或通过转换为会话时区），并且时区信息被丢弃。它不再指绝对时间。这就是为什么您通常不想像timestamp通常使用的那样存储时间戳的原因——也许一部电影会在每个时区timestamptz的特定日期的下午 6 点上映，这就是这种用例。

    如果您只在一个时区工作，您可能会（误）使用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 将默默地忽略任何时区指示

    • 125 
  2. SELECT extract(epoch from now() at time zone 'utc');

    不会返回正确的时间戳，因为 postgres 时区转换会从结果中丢弃时区信息：

    9.9.3。在时区

    语法：timestamp without time zone AT TIME ZONE zone
    返回：timestamp with time zone将不带时区
    的给定时间戳视为位于指定时区

    语法：带时区的时间戳 AT TIME ZONE zone
    返回：不带时区的时间戳将带时区
    的给定时间戳转换为新时区，不指定时区

    之后，extract 会查看没有时区的时间戳，并认为它是本地时间（尽管实际上它已经是 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执行转换，第二个为结果分配新的时区。

    • 32

相关问题