We can extract the date from a timestamp column in the following ways:
DATE() method::date suffixTO_CHAR() methodDATE_TRUNC() methodDATE() methodWe use the DATE() method to extract the date part of a date or
date/time or timestamp expression.
select DATE('2022-03-17 01:18:30') as date;
::date suffixWe use the ::date suffix to cast the timestamp column to the date type.
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;
TO_CHAR() methodThe 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.
select TO_CHAR('2022-03-17 01:18:30'::timestamp, 'yyyy-mm-dd') as date;
DATE_TRUNC() methodWe 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.
select date_trunc('day', '2022-03-17 01:18:30'::timestamp) as date;