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

如何从PostgreSQL获取正确的Unix时间戳？ .1e5b.de我没有从PostgreSQL获得预期的时间：

这返回了正确的时间戳：

SELECT extract(epoch from now());


，但是没有得到。 t：

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


#1 楼

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

从postgres timestamptz（如now()）获取Unix时间戳很简单，就像您说的那样：
这实际上是从timestamptz类型（包括now()）中获取绝对时间所需要了解的所有信息。

将timestamp之类的timestamptz数据放入该字段时，它将首先转换为特定时区（通过now()显式或通过转换为会话时区），并且时区信息将被丢弃。它不再是指绝对时间。这就是为什么您通常不希望将时间戳记存储为at time zone并通常使用timestamp的原因-也许电影是在每个时区的特定日期的晚上6点发布的，这就是用例。

如果您只在一个时区工作，则可能会误用timestamptz。转换回timestamp足够聪明，可以应付DST，并且出于转换目的，假定时间戳记在当前时区中。这是GMT / BST的示例：

select extract(epoch from now());


DBFiddle

但是，请注意以下令人困惑的行为：

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

这是因为：


PostgreSQL在确定其类型之前从未检查过文字字符串的内容，因此会将[…]都视为没有时区的时间戳。为了确保将文字视为带时区的时间戳，请为其提供正确的显式类型…在已确定为不含时区的时间戳中，PostgreSQL将默默地忽略任何时区指示


评论

任何想法如何将所得的十进制转换为不带小数点的整数（我的意思是将数字和十进制合并为一个大整数）。谢谢。

– W.M.
17年10月1日在19:19
像这样，但我敢肯定您真的不想这样做。也许您想乘以10的幂并去除任何剩余的小数？

–杰克·道格拉斯（Jack Douglas）
17年10月1日在22:16
@ W.M。也许是这样吗？ SELECT FLOOR（EXTRACT（epoch FROM NOW（））* 1000）;

– Joe23
18年7月12日在9:11

#2 楼

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

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

9.9.3。 AT TIME ZONE
语法：不带时区的时间戳记AT TIME ZONE区域
返回：带时区的时间戳记
给定时间戳记，不包含位于指定时区的时区
语法：带有时区AT时区的时间戳
返回：不带时区的时间戳
将给定的带有时区的时间戳转换为新的时区，而没有指定时区

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