主页 / dba / 问题 / 2758
Accepted
Jonas
Asked: 2011-05-18 01:16:03 +0800 CST

如何在几毫秒内从 PostgreSQL 获取时间戳列？

  • 772

我timestamp without time zone default now()在 PostgreSQL 数据库中有一个类型为“created”的列。

如果我选择列，默认情况下它具有良好且可读的格式：

SELECT created FROM mytable;

         created
---------------------------
2011-05-17 10:40:28.876944

但我想在几毫秒内得到时间戳（作为一个长）。像这样的东西：

从我的表中选择我的格式（创建）；

     created
-----------------
2432432343876944

如何在几毫秒内从 PostgreSQL 获取时间戳列？

对杰克的回应：

我确实得到了与您相同的差异（-3600），但是如果我使用，timestamp with time zone我可以看到“错误”或差异是因为 '1970-01-01' 获取 time zone +01。

create table my_table_2(created timestamp with time zone);
CREATE TABLE
insert into my_table_2 (created) values (now()), ('1970-01-01');
INSERT 0 2
select created, extract(epoch from created) from my_table_2;
            created            |    date_part
-------------------------------+------------------
 2011-05-18 11:03:16.909338+02 | 1305709396.90934
 1970-01-01 00:00:00+01        |            -3600
(2 rows)

差异是错误吗？我现在可能是因为“夏令时”？

to_timestamp()用于插入时间戳 0 和 1 时也很有趣。

insert into my_table_2 (created) values (to_timestamp(0));
INSERT 0 1

insert into my_table_2 (created) values (to_timestamp(1));
INSERT 0 1
select created, extract(epoch from created) from my_table_2;
            created            |    date_part
-------------------------------+------------------
 2011-05-18 11:03:16.909338+02 | 1305709396.90934
 1970-01-01 00:00:00+01        |            -3600
 1970-01-01 01:00:00+01        |                0
 1970-01-01 01:00:01+01        |                1
postgresql timestamp

3 个回答

  1. Best Answer

    使用EXTRACT和 UNIX 时间戳

    SELECT EXTRACT(EPOCH FROM TIMESTAMP '2011-05-17 10:40:28.876944') * 1000;

    会给

    1305621628876.94

    将其乘以1000将其转换为毫秒。然后，您可以将其转换为您想要的任何内容（十进制是一个不错的选择）。不要忘记记住时区。JackPDouglas 在他的回答中有这样一个例子。以下是他回答的摘录（created带有时间戳的列），说明了如何使用时区：

    SELECT EXTRACT(EPOCH FROM created AT TIME ZONE 'UTC') FROM my_table;
    • 65

  2. - 编辑 -

    我发现这（见下文）基本上是错误的。请参阅如何从 PostgreSQL 获取当前的 unix 时间戳？对于我的困惑的根源......

    --结束编辑--

    发布为答案，因为它不能作为评论。

    试验台：

    create role stack;
grant stack to dba;
create schema authorization stack;
set role stack;

create table my_table(created timestamp);
insert into my_table(created) values(now()),('1970-01-01');
\d my_table
              Table "stack.my_table"
 Column  |            Type             | Modifiers
---------+-----------------------------+-----------
 created | timestamp without time zone |

    查询：

    select created, extract(epoch from created) from my_table;

          created          |    date_part
---------------------------+------------------
 2011-05-17 13:18:48.03266 | 1305634728.03266
 1970-01-01 00:00:00       |            -3600


select created, extract(epoch from date_trunc('milliseconds', created)) 
from my_table;

          created          |    date_part
---------------------------+------------------
 2011-05-17 13:18:48.03266 | 1305634728.03266
 1970-01-01 00:00:00       |            -3600


select created, extract(epoch from created at time zone 'UTC') from my_table;

          created          |    date_part
---------------------------+------------------
 2011-05-17 13:18:48.03266 | 1305638328.03266
 1970-01-01 00:00:00       |                0

    注意date_part第三个查询是： 130563 83 28.03266 - 3600 不同。

    • 7

  3. 我知道这是一篇旧帖子，但看到我现在有同样的问题，并且正在参考这篇文章之类的东西，也许我的回答对其他人很有价值：

    将此附加到带有您正在查询的时间戳的列中：::timestamptz(3)

    这将过滤精度以仅检索/匹配毫秒->

    因此，例如，我在 JS 中的 SQL 解决方案现在正在运行，如下所示：

    `SELECT * FROM public.reports WHERE id::timestamptz(3) = '${dateRequested}';`
    • 2

相关问题