PostgreSQL 时间与Unix时间戳
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)
#
页:
[1]