How to Extract the Date from a Timestamp in PostgreSQL?

Extracting Date from Timestamp in PostgreSQL

When working with timestamps in PostgreSQL, there may be instances when you need to extract only the date component. Here are the optimal approaches to achieve this:

Using ::date Cast

The simplest method is to append the ::date cast to your timestamp. This converts it directly to a DATE data type, removing the time component:

SELECT timestamp_field::date FROM table_name;

Example

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

Output:
2011/05/26

Using date_trunc Function (With Time Zone Information)

Alternatively, you can employ the date_trunc function. While it retains the timestamp's data type, the time zone information is preserved:

SELECT date_trunc('day', timestamp_field) FROM table_name;

Example

SELECT date_trunc('day', '2010-01-01 12:00:00'::timestamp);

Output:
2010-01-01 12:00:00+02

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