Extract date (yyyy/mm/dd) from a timestamp in PostgreSQL

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

Learn various methods to accurately extract only the date portion (YYYY/MM/DD) from timestamp and timestamptz columns in PostgreSQL, covering casting, functions, and formatting.

PostgreSQL is a powerful relational database system, and working with dates and times is a common task. Often, you'll store full timestamp information, but for reporting or specific queries, you only need the date part (year, month, and day) without the time. This article explores several robust methods to achieve this, from simple casting to using specialized functions and formatting options.

1. Using Simple Type Casting

The most straightforward way to extract the date from a TIMESTAMP or TIMESTAMPTZ column in PostgreSQL is by casting it directly to a DATE type. PostgreSQL handles this conversion efficiently, automatically truncating the time component.

SELECT
    your_timestamp_column AS original_timestamp,
    your_timestamp_column::date AS extracted_date
FROM
    your_table;

Casting a timestamp column to a date type using the :: operator.

2. Utilizing the DATE_TRUNC() Function

The DATE_TRUNC() function allows you to truncate a timestamp to a specified precision (e.g., 'year', 'month', 'day', 'hour'). While it returns a TIMESTAMP type, you can then cast it to DATE to get the desired result. This is particularly useful if you need to truncate to different units before getting the date.

SELECT
    your_timestamp_column AS original_timestamp,
    DATE_TRUNC('day', your_timestamp_column)::date AS extracted_date
FROM
    your_table;

Using DATE_TRUNC('day', ...) to truncate to the beginning of the day, then casting to date.

3. Formatting with TO_CHAR()

For scenarios where you need the date in a specific string format (e.g., 'YYYY/MM/DD', 'MM-DD-YYYY'), the TO_CHAR() function is invaluable. It converts a timestamp to a string based on a provided format model.

SELECT
    your_timestamp_column AS original_timestamp,
    TO_CHAR(your_timestamp_column, 'YYYY/MM/DD') AS formatted_date_string
FROM
    your_table;

Using TO_CHAR() to get the date as a string in 'YYYY/MM/DD' format.

4. Extracting Components with EXTRACT()

The EXTRACT() function allows you to retrieve individual components (like year, month, day) from a date or timestamp. While you can extract YEAR, MONTH, and DAY separately, reconstructing them into a DATE type requires additional steps, often using MAKE_DATE().

SELECT
    your_timestamp_column AS original_timestamp,
    EXTRACT(YEAR FROM your_timestamp_column) AS year_part,
    EXTRACT(MONTH FROM your_timestamp_column) AS month_part,
    EXTRACT(DAY FROM your_timestamp_column) AS day_part,
    MAKE_DATE(
        EXTRACT(YEAR FROM your_timestamp_column)::integer,
        EXTRACT(MONTH FROM your_timestamp_column)::integer,
        EXTRACT(DAY FROM your_timestamp_column)::integer
    ) AS reconstructed_date
FROM
    your_table;

Extracting year, month, and day, then reconstructing a DATE using MAKE_DATE().

Decision flow for choosing the right date extraction method.

Each method has its strengths and ideal use cases. For simple date extraction into a DATE data type, direct casting is the most efficient. When specific string formatting is required, TO_CHAR() is the go-to. DATE_TRUNC() offers flexibility for truncating to other units, and EXTRACT() is best when you need individual date components.

1. Step 1

Understand your output requirement: Do you need a DATE type, or a TEXT (string) representation?

2. Step 2

For DATE type: Prefer your_timestamp_column::date for simplicity and performance.

3. Step 3

For DATE type with specific truncation before final date: Consider DATE_TRUNC('day', your_timestamp_column)::date.

4. Step 4

For TEXT (string) output with custom formatting: Use TO_CHAR(your_timestamp_column, 'YYYY/MM/DD').

5. Step 5

Avoid EXTRACT() for direct date extraction unless you specifically need individual components for complex logic, as it requires more steps to reconstruct a DATE.