Converting Timestamps to Dates Simplified

PostgreSQL: Convert Timestamp to Date Made Simple

Learn how to convert timestamps to dates in PostgreSQL using simple SQL queries. Explore examples and best practices for handling date transformations.

How to Convert Timestamps to Dates in PostgreSQL

PostgreSQL provides powerful tools for date and time manipulation. A common requirement is converting a timestamp to a date, effectively removing the time part while retaining the date.

1. Using the ::DATE Cast Operator

The simplest method is casting the timestamp column to date.

SELECT your_timestamp_column::DATE AS date_only  
FROM your_table;  

2. Using the DATE() Function

The DATE() function achieves the same result, extracting the date part of the timestamp.

SELECT DATE(your_timestamp_column) AS date_only  
FROM your_table;  

3. Practical Example

Suppose you have a table called orders with a created_at column (of type timestamp). To display only the date:

Code:

SELECT created_at, created_at::DATE AS order_date  
FROM orders;  

Output:

created_at	order_date
2024-11-07 14:30:00	2024-11-07
2024-11-06 09:15:45	2024-11-06

4. Converting Timestamps in WHERE Clauses

You can also use these methods in filtering conditions. For example, fetching records for a specific date:

Code:

SELECT *  
FROM orders  
WHERE created_at::DATE = '2024-11-07';  

Additional Tips:

• Performance: Casting (::DATE) is often faster than using functions like DATE().
• Index Usage: Ensure the use of indices for optimized query performance when filtering by date.

All PostgreSQL Questions, Answers, and Code Snippets Collection.