1.unix时间戳---》 时间
成都创新互联公司网站建设服务商，为中小企业提供成都网站制作、成都做网站、外贸营销网站建设服务，网站设计，网站托管等一站式综合服务型公司，专业打造企业形象网站，让您在众多竞争对手中脱颖而出成都创新互联公司。
秒
# SELECT TIMESTAMP WITH TIME ZONE 'epoch' + 1471313356 * INTERVAL '1 second';
?column?
------------------------
2016-08-16 10:09:16+08
(1 row)
# SELECT TIMESTAMP WITHOUT TIME ZONE 'epoch' + 1471313356 * INTERVAL '1 second';
?column?
---------------------
2016-08-16 02:09:16
(1 row)
# SELECT TIMESTAMP WITH TIME ZONE 'epoch' + 1461033436.269 * INTERVAL '1 second';
?column?
----------------------------
2016-04-19 10:37:16.269+08
(1 row)
毫秒
# SELECT TIMESTAMP WITH TIME ZONE 'epoch' + 1464186650352 * INTERVAL '1 milliseconds';
?column?
----------------------------
2016-05-25 22:30:50.352+08
(1 row)
# SELECT TIMESTAMP WITHOUT TIME ZONE 'epoch' + 1464186650352 * INTERVAL '1 milliseconds';
?column?
-------------------------
2016-05-25 14:30:50.352
(1 row)
#
2.时间----》 Unix时间戳
秒
# SELECT EXTRACT(EPOCH FROM TIMESTAMP WITHOUT TIME ZONE '2016-05-25 14:30:50.352');
date_part
----------------
1464186650.352
(1 row)
# SELECT EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE '2016-05-25 14:30:50.352');
date_part
----------------
1464157850.352
(1 row)
毫秒
# SELECT EXTRACT(EPOCH FROM TIMESTAMP WITHOUT TIME ZONE '2016-05-25 14:30:50.352')*1000;
?column?
---------------
1464186650352
(1 row)
#
postgres=# SELECT extract(epoch FROM '2017-02-07 11:18:23.098+08'::timestamp with time zone);
date_part
----------------
1486437503.098
(1 row)
postgres=# select to_timestamp(1486437503.098);
to_timestamp
----------------------------
2017-02-07 11:18:23.098+08
(1 row)
postgres=#
