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)