PostgreSQL: Combine Date and Time into Timestamp

I’ve worked with systems where DATE and TIME are stored separately—maybe for legacy reasons, or because different parts of the application manage them independently. But for real-world queries, you almost always need the full TIMESTAMP: the moment in time when something actually happened. Combining separate date and time columns is straightforward in PostgreSQL, and there are actually multiple ways to do it, each with subtle differences.

The key insight is that PostgreSQL understands DATE and TIME as separate types with operators that let you combine them. You have a few options—some faster, some more explicit—and knowing which to use depends on your specific situation.

Quick Answer: Use DATE + TIME Operator

To combine separate DATE and TIME columns:

SELECT
    date_col,
    time_col,
    date_col + time_col as combined_timestamp
FROM events
LIMIT 5;

Returns:

date_col   | time_col | combined_timestamp
----------+----------+---------------------
2026-02-21| 10:30:45 | 2026-02-21 10:30:45
2026-02-20| 14:15:30 | 2026-02-20 14:15:30

The + operator directly combines DATE and TIME types into a TIMESTAMP. It’s the simplest and fastest approach.

How It Works

PostgreSQL has a built-in + operator that works on DATE and TIME types. When you add a DATE and TIME together, PostgreSQL treats the operation as type coercion and returns a TIMESTAMP. This is the most direct method because you’re not converting to text and back—you’re directly combining the types.

Alternatively, you can concatenate as text and cast to TIMESTAMP: (date_col || ' ' || time_col)::TIMESTAMP. This is more explicit about what you’re doing (string concatenation followed by casting), but it’s slightly slower because PostgreSQL has to parse the resulting text string.

Different Approaches & When to Use Them

Approach 1: Direct Addition with + Operator (Fastest)

The most straightforward approach using PostgreSQL’s built-in operator:

SELECT
    event_id,
    event_date,
    event_time,
    event_date + event_time as event_timestamp,
    (event_date + event_time) AT TIME ZONE 'UTC' as utc_timestamp
FROM events
WHERE event_date IS NOT NULL
AND event_time IS NOT NULL;

This is what I use most of the time. It’s fast, clean, and clearly conveys intent. PostgreSQL optimizes this directly.

Approach 2: Concatenation and Cast (More Explicit)

When you prefer explicit text operations:

SELECT
    date_col,
    time_col,
    (date_col::TEXT || ' ' || time_col::TEXT)::TIMESTAMP as combined_timestamp,
    (date_col::TEXT || ' ' || time_col::TEXT)::TIMESTAMP WITH TIME ZONE as with_timezone
FROM events;

This approach is more readable to some developers because it explicitly shows the concatenation step. It’s slightly slower because PostgreSQL parses the resulting string.

Approach 3: Concatenate with Padding for Consistency

When you’re concerned about format consistency:

SELECT
    event_date,
    event_time,
    (event_date::TEXT || ' ' || LPAD(event_time::TEXT, 8, '0'))::TIMESTAMP as standardized
FROM events;

This ensures the time is always 8 characters (HH:MM:SS format), useful if your TIME column might have variable format.

Approach 4: With Timezone Application

Combining and immediately converting to a specific timezone:

SELECT
    date_col,
    time_col,
    (date_col + time_col) AT TIME ZONE 'US/Eastern' as eastern_time,
    (date_col + time_col) AT TIME ZONE 'UTC' as utc_time,
    (date_col + time_col) AT TIME ZONE 'Europe/London' as london_time
FROM events;

Perfect when you need to apply timezone context immediately after combining.

Approach 5: Conditional Combination

When some rows might have NULL values:

SELECT
    event_id,
    event_date,
    event_time,
    CASE
        WHEN event_date IS NOT NULL AND event_time IS NOT NULL
        THEN event_date + event_time
        WHEN event_date IS NOT NULL
        THEN event_date::TIMESTAMP
        ELSE NULL
    END as event_timestamp
FROM events;

Handles NULL cases gracefully instead of returning NULL for the whole row.

Real-World Example: Appointment Scheduling System

Let me walk you through a realistic scenario. You’re building an appointment booking system where the calendar date and time slot are stored separately:

SELECT
    appointment_id,
    patient_id,
    patient_name,
    appointment_date,
    appointment_time,
    appointment_date + appointment_time as appointment_timestamp,
    CASE
        WHEN (appointment_date + appointment_time) < NOW()
        THEN 'Past'
        WHEN (appointment_date + appointment_time) < NOW() + INTERVAL '1 hour'
        THEN 'Imminent'
        WHEN (appointment_date + appointment_time) < NOW() + INTERVAL '24 hours'
        THEN 'Today'
        ELSE 'Upcoming'
    END as appointment_status,
    (appointment_date + appointment_time) AT TIME ZONE 'America/New_York' as appointment_eastern
FROM appointments
WHERE appointment_date >= CURRENT_DATE
ORDER BY appointment_date + appointment_time;

Here you’re combining the date and time, checking the appointment status, and converting to a specific timezone. This is exactly what a real appointment system does.

FAQ

Q: Which method is fastest—the + operator or concatenation and cast? A: The + operator is fastest because PostgreSQL treats it as a direct type operation without string parsing. Use date_col + time_col for best performance.

Q: How do I add timezone after combining? A: Use AT TIME ZONE after the combination: (date_col + time_col) AT TIME ZONE 'America/New_York'. This treats the combined timestamp as being in the specified timezone.

Q: What if one of my columns is NULL? A: The result is NULL. Use COALESCE() or CASE WHEN to provide defaults: COALESCE(date_col + time_col, CURRENT_TIMESTAMP).

Q: Can I separate a TIMESTAMP back into DATE and TIME? A: Yes, use DATE(timestamp_col) to extract the date and TIME(timestamp_col) or extract(time from timestamp_col) to extract the time.

Q: What if my TIME column has timezone info? A: TIME WITH TIME ZONE exists but is rarely used. Usually, you apply timezone context when combining: (date_col + time_col) AT TIME ZONE 'UTC'.

Q: Does concatenation and cast handle different formats? A: It depends on your PostgreSQL datestyle settings. The + operator is safer because it doesn’t depend on date/time format configuration.

Q: What about milliseconds or microseconds in the TIME? A: TIME can include microseconds (HH:MM:SS.SSSSSS). The + operator handles this automatically.

Q: Can I combine and round to the nearest 5 minutes? A: Yes: DATE_TRUNC('5 minutes', date_col + time_col) rounds the combined timestamp to the nearest 5-minute interval.

Q: Performance impact on millions of rows? A: Negligible. Both methods are fast. The + operator is slightly faster but the difference is microseconds per row.

Q: Should I store a separate TIMESTAMP column, or combine on-the-fly? A: If you query frequently, consider storing the combined TIMESTAMP. If data is rarely queried, combining on-the-fly saves storage and keeps data normalized.

Common Errors & Solutions

Error: “Cannot add DATE and TIME” The columns might not be the correct types. Check: \d table_name in psql. If they’re TEXT, cast first: date_col::DATE + time_col::TIME.

Error: “Result is NULL unexpectedly” One of your columns is probably NULL. Always filter: WHERE date_col IS NOT NULL AND time_col IS NOT NULL.

Error: “Timezone conversion gives unexpected results” Remember: AT TIME ZONE interprets the timestamp as being in the given timezone, not converting it. Use AT TIME ZONE 'UTC' AT TIME ZONE 'America/New_York' to convert properly.

Performance Tips

  • Use + operator: date_col + time_col is fastest and most direct.
  • Concatenation is fine too: If readability matters more, the performance difference is negligible.
  • Combine once, reuse: If you combine the same columns repeatedly, create a computed column or view.
  • Index the result: If you filter frequently by the combined timestamp, consider creating an index: CREATE INDEX idx_event_ts ON events (event_date + event_time).
  • Avoid re-combining in filters: WHERE date_col + time_col > value works but won’t use indexes. Filter on the original columns when possible.

Wrapping Up

Combining DATE and TIME into TIMESTAMP is simple: just use the + operator. It’s fast, clean, and PostgreSQL handles it directly without text parsing. For most cases, date_col + time_col is all you need. If you need timezone context, add AT TIME ZONE immediately after. This is such a common operation that PostgreSQL optimized it thoroughly—trust the database to do it right.