Postgresql中时间戳与日期的相互转换

• --日期转时间戳

-----具体精度运行可见

SELECT EXTRACT(epoch FROM CAST('20191025' AS TIMESTAMP))
union all
SELECT EXTRACT(epoch FROM CAST('20191025 01:00:08' AS TIMESTAMP))
union all
SELECT EXTRACT(epoch FROM CAST('now()' AS TIMESTAMP))
           

----上述也可简化    ::TIMESTAMP 代替cast

SELECT EXTRACT(epoch FROM '20191025'::TIMESTAMP)
union all
SELECT EXTRACT(epoch FROM '20191025 01:00:08'::TIMESTAMP)
union all
SELECT EXTRACT(epoch FROM now())
           

• --时间戳转日期

SELECT TO_TIMESTAMP(1571961600)