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));

Extract date from a timestamp

Need a good GUI tool for PostgreSQL? Check out TablePlus. It’s native, beautiful, and available for free.

Download TablePlus for Mac.

Not on Mac? Download TablePlus for Windows.

On Linux? Download TablePlus for Linux

Need a quick edit on the go? Download TablePlus for iOS

TablePlus GUI Tool PostgreSQL