When working with timestamps in PostgreSQL, you often need to extract just the date portion, discarding the time information. PostgreSQL provides several elegant methods to accomplish this task.

The simplest and most straightforward approach is to use the date type cast. You can convert a timestamp to a date by simply adding ::date after your timestamp column or value:


SELECT timestamp_column::date FROM your_table;

Let’s understand what happens under the hood. When you perform this cast, PostgreSQL truncates the time portion (hours, minutes, seconds) from your timestamp, keeping only the year, month, and day components. This operation is particularly useful when you need to group data by date or compare timestamps regardless of their time portions.

For those who prefer more explicit function calls, PostgreSQL also offers the date_trunc function:


SELECT date_trunc('day', timestamp_column) FROM your_table;

While both methods effectively extract the date, they have a subtle difference. The ::date cast returns a DATE type, while date_trunc returns a TIMESTAMP with the time portion set to midnight (00:00:00).

Which method should you choose? The type cast (::date) is more concise and often more performant, making it the preferred choice for most scenarios. However, if you need to maintain timestamp precision while zeroing out the time portion, date_trunc would be your go-to solution.

Remember that these operations work seamlessly with both timestamp and timestamptz (timestamp with time zone) data types, though the results might differ slightly due to timezone considerations.

More articles on this topic:

Categorized in:

Short answers,

Last Update: 22/01/2025