PostgreSQL Cast Timestamp To Date

As a database consultant working with PostgreSQL, I’ve recently had a requirement to convert timestamp values to dates for one of the applications. In this comprehensive guide, I’ll share the multiple methods to convert PostgreSQL timestamps to dates, including the pros and cons of each approach.

PostgreSQL Cast Timestamp To Date

Before diving into conversion methods, let’s clarify what we’re working with:

PostgreSQL Timestamp: Stores both date and time values with microsecond (e.g., 2025-05-15 14:30:45.123456)

PostgreSQL Date: Stores only the date portion without time information (e.g., 2025-05-15)

When you need to analyze data by day rather than specific moments, converting timestamps to dates becomes necessary. Let’s explore multiple approaches to achieving this requirement.

Approach 1: Using the CAST Function

The most straightforward method I use for converting a timestamp to a date is PostgreSQL’s CAST function using the below query.

SELECT 
    CAST(sale_date AS DATE) AS sales_date,
    COUNT(*) AS sales
FROM Sales
GROUP BY CAST(sale_date AS DATE)
ORDER BY sale_date;

After executing the above query, I got the expected output as shown in the screenshot below.

Benefits of Using CAST:

• Clear, explicit syntax that makes the intention obvious
• Standard SQL approach that works across various database systems
• No performance issues when properly indexed

Approach 2: Using the :: Operator (PostgreSQL Shorthand)

The :: operator is PostgreSQL’s shorthand syntax for casting. You can use the below query.

SELECT 
    sale_date::DATE AS sale_date,
    SUM(amount) AS daily_revenue
FROM sales
GROUP BY sale_date::DATE
ORDER BY sale_date;

After executing the above query, I got the expected output as shown in the screenshot below.

Approach 3: Using the DATE() Function

PostgreSQL also offers a DATE() function for timestamp conversion.

SELECT 
    DATE(logged_at) AS log_date,
    COUNT(*) AS error_count
FROM system_logs
WHERE log_level = 'Error'
GROUP BY DATE(logged_at)
ORDER BY log_date DESC;

I got the expected output after executing the above query as shown in the screenshot below.

Benefits of the DATE() Function:

• Semantically clear – the function name directly expresses the intention
• Works well in complex expressions
• Consistent with other date-related functions

Approach 4: Using the date_trunc Function

We can use the date_trunc Function with the following query when working with time series data.

SELECT 
    date_trunc('day', sale_date) AS sale_date,
    COUNT(*) AS sales_count
FROM sales
GROUP BY date_trunc('day', sale_date)
ORDER BY sale_date;

Check out the screenshot below, which shows that I got the expected output after executing the above query.

When to Use date_trunc:

• When you need to group by different time periods (days, weeks, months)
• For time series analysis requiring different granularity levels
• When you want a timestamp at the beginning of the day (00:00:00) rather than just a date

Note that date_trunc('day', timestamp) returns a timestamp set to midnight (00:00:00) of the specified day, not a date type. If you strictly need a date type, you’ll need to cast the result:

SELECT date_trunc('day', sale_date)::DATE AS sale_date
FROM sales;

After executing the above query, I got the expected result as shown in the screenshot below.

Approach 5: Using TO_CHAR()

Sometimes I need to convert a timestamp to a formatted date string. The TO_CHAR() function is perfect for this scenario: Let us use the query below.

SELECT 
    TO_CHAR(sale_date, 'YYYY-MM-DD') AS sale_date_str,
    SUM(amount) AS daily_sales
FROM sales
GROUP BY TO_CHAR(sale_date, 'YYYY-MM-DD')
ORDER BY sale_date_str;

I got the expected output after executing the above query.

When to Use TO_CHAR:

• When you need a specific string format for output
• For reports and user interfaces requiring custom date formats
• When integrating with systems that expect dates in a specific string format

Performance Considerations When Casting Timestamps

Performance becomes crucial when working with large datasets.

Here’s what I’ve learned about performance when casting timestamps to dates:

Indexing Strategy

If you frequently filter or group by the date portion of a timestamp column, consider creating a functional index:

CREATE INDEX idx_sale_date ON sales (CAST(sale_date AS DATE));
-- or
CREATE INDEX idx_sale_date ON sales ((sale_date::DATE));

Comparison of Method Performance

Here’s how the methods compare:

Method	Performance	Memory Usage	Use Case
CAST	Excellent	Minimal	General purpose, indexed operations
:: operator	Excellent	Minimal	PostgreSQL-specific code, readability
DATE()	Excellent	Minimal	Complex expressions
date_trunc	Good	Slightly higher	Time-series analysis, multiple granularities
TO_CHAR	Moderate	Higher	Formatted output, specific string formats

Conclusion

After implementing timestamp-to-date conversions for many projects, here’s my advice:

1. For general-purpose conversion, use ::DATE for its simplicity and readability
2. For time-series analysis requiring different levels of granularity, use date_trunc
3. For custom formatting needs, use TO_CHAR()
4. Always consider performance implications for large datasets
5. Create functional indexes for frequently filtered or grouped date extractions

Selecting the appropriate conversion method for your requirement will surely improve query performance and code maintainability.

You may also like the following articles.

• Postgres date range
• PostgreSQL current_timestamp
• PostgreSQL Order By Date