How to Extract YYYY/MM/DD Dates from Timestamps in PostgreSQL?

Extracting the Date (YYYY/MM/DD) from Timestamps in PostgreSQL

To retrieve solely the date portion from timestamps in PostgreSQL, utilize the following methods:

1. Type Casting:

   • Append the suffix "::date" to your timestamp to cast it to a DATE data type. This method directly converts the timestamp to the desired format without additional processing.

     SELECT '2010-01-01 12:00:00'::timestamp::date;

2. Date Truncation Function:

   • Employ the "date_trunc" function to truncate a timestamp to a specific date unit (e.g., day). This method preserves the timestamp's time zone, if necessary.

     SELECT date_trunc('day', now());

Example: Extracting Date from a Timestamp and Inserting into a DATE Column

To insert the extracted date (YYYY/MM/DD) into a DATE column in another table, use the following steps:

1. Utilize any of the two aforementioned methods to extract the date from the timestamp.
2. Insert the extracted date into the desired column in the target table.

Example:

-- Extract the date from a timestamp
SELECT '2011/05/26 09:00:00'::timestamp::date;

-- Insert the extracted date into a DATE column
INSERT INTO my_table (date_column) VALUES ('2011-05-26');

The above is the detailed content of How to Extract YYYY/MM/DD Dates from Timestamps in PostgreSQL?. For more information, please follow other related articles on the PHP Chinese website!