How to Extract the Date (yyyy/mm/dd) from a Timestamp in PostgreSQL?

Extracting Date (yyyy/mm/dd) from a Timestamp in PostgreSQL

Several methods can be used to extract the date (yyyy/mm/dd) from a timestamp in PostgreSQL.

Using Casting:

To cast the timestamp directly to a date, append ::date to the timestamp:

SELECT '2011/05/26 09:00:00'::date;

This will return the date component as a Postgresql DATE type:

2011/05/26

Using to_char() and to_date():

Another approach is to convert the timestamp to a string using to_char() and then back to a date using to_date():

SELECT to_date(to_char(timestamp, 'YYYY/MM/DD'), 'YYYY/MM/DD') FROM val3 WHERE>

Using the date_trunc() Function:

The date_trunc() function can also be used to extract the date component from a timestamp while preserving the time zone:

SELECT date_trunc('day', timestamp) FROM val3 WHERE>

This will return the date component as a Postgresql DATE type with the same time zone as the original timestamp.

The above is the detailed content of How to Extract the Date (yyyy/mm/dd) from a Timestamp in PostgreSQL?. For more information, please follow other related articles on the PHP Chinese website!