我在Postgresql数据库中有一个“创建”列,其类型为timestamp,时区默认为now().

如果我选择colums,它默认情况下具有漂亮且可读的格式：

SELECT created FROM mytable;

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

但我想在几毫秒内得到时间戳(如长).像这样的东西：

选择myformat(created)FROM mytable;

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

如何从Postgresql获取timestamp列只需几毫秒？

回应杰克：

我确实得到了与你相同的差异(-3600),但是如果我使用带时区的时间戳,我可以看到“错误”或差异是因为’1970-01-01’得到时区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

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

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