How to extract the date from a column in Postgres
Overview
We can extract the date from a timestamp column in the following ways:
- The
DATE()method - The
::datesuffix - The
TO_CHAR()method - The
DATE_TRUNC()method
The DATE() method
We use the DATE() method to extract the date part of a date or
date/time or timestamp expression.
Example
select DATE('2022-03-17 01:18:30') as date;
The ::date suffix
We use the ::date suffix to cast the timestamp column to the date type.
Example
In the below code, we first convert the string to a timestamp by suffixing it with ::timestamp suffix. Then we convert the timestamp to a date by suffixing it with ::date.
select '2022-03-17 01:18:30'::timestamp::date;
The TO_CHAR() method
The TO_CHAR() method returns a string in a TEXT data type representing the first argument formatted according to the specified format.
We can use TO_CHAR() to convert the timestamp to date by specifying the format as yyyy-mm-dd.
Example
select TO_CHAR('2022-03-17 01:18:30'::timestamp, 'yyyy-mm-dd') as date;
The DATE_TRUNC() method
We use the DATE_TRUNC() method to truncate the TIMESTAMP or an INTERVAL value and return the truncated timestamp or interval with a level of precision with the below syntax:
date_trunc('datepart', field)
The datepart can be day, second, month, and so on.
Example
select date_trunc('day', '2022-03-17 01:18:30'::timestamp) as date;