To extract a date (yyyy-mm-dd) from a timestamp value

For example, you want to extract from '2018-07-25 10:30:30' to '2018-07-25'

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 '2018-07-25 10:30:30'::TIMESTAMP::DATE;

Or combine date() and substring() function:

SELECT DATE(SUBSTRING('2018-07-25 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.

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

TablePlus GUI Tool PostgreSQL