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

Extract date from a timestamp

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

TablePlus GUI Tool PostgreSQL