PostgreSQL: Convert Timestamp to Date

Converting a timestamp to a date in PostgreSQL is as simple as a cast, but doing it safely across time zones, performance-friendly in large datasets, and readable for future maintainers takes some nuance.

This glossary entry shows you why and how to strip the time portion from a timestamp, explores multiple techniques, explains hidden edge cases like daylight-saving shifts, and provides production-grade patterns you can paste directly into Galaxy’s SQL editor.

Why You Might Need to Convert Timestamps to Dates

Most analytical questions revolve around calendar days—daily active users, orders per day, or error counts by deployment date. Yet event logs and transactional tables typically record timestamps, precise to the millisecond (or even microsecond). Dropping the time component ensures that identical calendar days group together, which simplifies joins, aggregations, and partition pruning.

Core Techniques

1. The Cast Operator (::date)

The quickest approach is a direct cast:

SELECT created_at::date AS order_date
FROM orders;

This is syntactic sugar for the SQL standard CAST(created_at AS date). PostgreSQL simply truncates the time portion in the session’s current time zone.

2. The DATE() Function

PostgreSQL provides a date() wrapper, functionally identical to the cast:

SELECT date(created_at) AS order_date
FROM orders;

Some teams prefer the function form because it looks explicit in report definitions.

3. Time-Zone Aware Conversion

If your created_at column stores TIMESTAMP WITH TIME ZONE (alias timestamptz) and your analysis must be done in a specific zone, apply AT TIME ZONE before casting:

SELECT (created_at AT TIME ZONE 'America/Los_Angeles')::date AS order_date_pst
FROM orders;

Why? The implicit cast converts in the session’s zone, which may vary by user or by application pool. Being explicit eliminates surprises, especially around daylight-saving transitions.

4. Using date_trunc() for Readability

date_trunc('day', ts) returns the timestamp at midnight. You still need ::date to get a date type:

SELECT date_trunc('day', created_at)::date AS order_date
FROM orders;

This pattern is handy when you need both the date and the truncated midnight timestamp in the same query.

Performance Considerations

Casting a timestamp column in a WHERE clause forces PostgreSQL to compute the cast row by row, bypassing column statistics and indexes. The classic anti-pattern looks like:

-- ☹️ Slow! Cannot use an index on created_at
SELECT *
FROM orders
WHERE created_at::date = CURRENT_DATE - INTERVAL '1 day';

Instead, cast the parameter or use a half-open date range:

-- 😊 Index-friendly
SELECT *
FROM orders
WHERE created_at >= (CURRENT_DATE - INTERVAL '1 day')::timestamp
AND created_at < CURRENT_DATE::timestamp;

With this pattern, PostgreSQL can leverage a B-tree index on created_at and prune partitions effectively.

Handling Epoch Values

Message queues and telemetry systems often store epoch seconds as integers. Convert them to date in two steps:

SELECT to_timestamp(epoch_seconds)::date AS metric_date
FROM events;

Skipping to_timestamp and casting the integer directly to date yields nonsense dates around 1970-01-01.

Real-World Example: Cohort Retention Query

Suppose you want to count how many users returned one day after signup. You need both signup date and first-return date, both derived from a timestamptz field in your events table.

WITH signup AS (
SELECT user_id,
MIN(event_time) AS first_seen,
MIN(event_time)::date AS signup_date
FROM events
WHERE event_name = 'signup'
GROUP BY user_id
),
next_visit AS (
SELECT user_id,
MIN(event_time) AS second_seen
FROM events
WHERE event_name = 'page_view'
GROUP BY user_id
)
SELECT signup_date,
COUNT(*) FILTER (
WHERE (second_seen::date - signup_date) = 1) AS retained_next_day,
COUNT(*) AS total_signed_up
FROM signup
LEFT JOIN next_visit USING (user_id)
GROUP BY signup_date
ORDER BY signup_date;

The query uses explicit ::date casts to align sign-up and follow-up events on calendar days.

Galaxy & Timestamp-to-Date Conversion

Galaxy’s AI copilot autocompletes ::date, suggests index-friendly filtering patterns, and warns when a conversion might miss a time-zone nuance. In a shared Collection, your team can endorse the above cohort query so that others reuse the proven date-handling logic without hunting through Slack threads.

Best Practices Checklist

• Be explicit about time zones. Cast after AT TIME ZONE when user context matters.
• Avoid casting indexed columns in predicates; cast literals instead or use range filters.
• Use half-open ranges (>= start, < next day) for roll-up queries.
• Document the pattern in Galaxy Collections so newcomers see the canonical approach.

Common Mistakes & How to Fix Them

1. Silent Time-Zone Drift

Mistake: Ignoring the session zone when casting timestamptz to date.
Fix: Pin the zone with AT TIME ZONE or set SET TIME ZONE 'UTC'; in the session.

2. Casting in WHERE Clauses

Mistake: Wrapping the column in ::date in filters, killing index usage.
Fix: Filter on the raw timestamp with a range boundary.

3. Misusing date_trunc

Mistake: Assuming date_trunc('day', ts) returns a date; it returns timestamp.
Fix: Append ::date or use DATE(ts) directly.

Further Reading

• PostgreSQL Documentation: Date/Time Types
• PostgreSQL Documentation: Date/Time Functions
• Galaxy Blog: Writing Time-Zone Safe SQL (upcoming)