PostgreSQL - How to extract date from a timestamp?
To extract a date (yyyy-mm-dd) from a timestamp value
For example, you want to extract from '2019-10-14 10:30:30'
to '2019-10-14'
1. Extract from a timestamp column:
Use date()
function:
SELECT DATE(column_name) FROM table_name;
2. Extract date from a specific timestamp value:
Cast the timestamp to a date by adding ::date
suffix:
SELECT '2019-10-14 10:30:30'::TIMESTAMP::DATE;
Or combine date()
and substring()
function:
SELECT DATE(SUBSTRING('2019-10-14 10:30:30' FROM 1 FOR 10));
Need a good GUI tool for PostgreSQL? Check out TablePlus. It’s native, beautiful, and available for free.
Not on Mac? Download TablePlus for Windows.
On Linux? Download TablePlus for Linux
Need a quick edit on the go? Download TablePlus for iOS