PostgreSQL: Add Hours to Timestamp

You need to calculate future times by adding hours. Maybe you’re scheduling reminders, calculating shift end times, or determining when a timeout occurs. Adding hours to timestamps is one of the most common time operations.

Quick Answer: Use + Operator with INTERVAL

To add hours to a timestamp, use the + operator with an INTERVAL:

SELECT
    emp_id,
    first_name,
    created_at,
    created_at + INTERVAL '6 hours' as six_hours_later,
    created_at + INTERVAL '24 hours' as one_day_later
FROM employees
WHERE created_at IS NOT NULL
LIMIT 5;

Returns:

emp_id | first_name | created_at          | six_hours_later     | one_day_later
-------+------------+---------------------+---------------------+---------------------
     1 | James      | 2026-02-21 10:30:45 | 2026-02-21 16:30:45 | 2026-02-22 10:30:45
     2 | Mary       | 2026-02-20 14:15:30 | 2026-02-20 20:15:30 | 2026-02-21 14:15:30

That’s it. Timestamp plus interval equals new timestamp.

How It Works

PostgreSQL treats INTERVAL as a duration. When you add it to a timestamp, PostgreSQL calculates the new point in time. The + operator knows how to handle all the complexity: day boundaries, month lengths, leap years — it all works automatically.

Different Approaches & When to Use Them

Approach 1: Simple Interval Addition (Most Common)

Straightforward hours addition:

SELECT
    created_at,
    created_at + INTERVAL '6 hours' as reminder_time,
    created_at + INTERVAL '24 hours' as next_day,
    created_at + INTERVAL '72 hours' as three_days_later
FROM events;

Best for: Standard time calculations.

Approach 2: Using MAKE_INTERVAL (For Variables)

When adding a dynamic number of hours from a column:

SELECT
    event_id,
    event_time,
    delay_hours,
    event_time + MAKE_INTERVAL(hours => delay_hours) as adjusted_time
FROM events
WHERE delay_hours > 0;

This is cleaner when the number of hours comes from a column or variable.

Best for: Dynamic hour calculations.

Approach 3: Subtract Hours Instead

SELECT
    created_at,
    created_at - INTERVAL '6 hours' as six_hours_earlier,
    created_at - INTERVAL '24 hours' as previous_day
FROM events;

Use - for subtraction instead of +.

Approach 4: Combine Hours with Other Units

Add hours along with minutes, days, or other intervals:

SELECT
    start_time,
    start_time + INTERVAL '2 hours 30 minutes' as end_with_break,
    start_time + INTERVAL '1 day 6 hours' as next_day_plus_6hrs
FROM shifts;

PostgreSQL handles complex intervals seamlessly.

Approach 5: Conditional Hour Addition

Add different hours based on logic:

SELECT
    ticket_id,
    created_at,
    priority,
    CASE
        WHEN priority = 'High' THEN created_at + INTERVAL '1 hour'
        WHEN priority = 'Medium' THEN created_at + INTERVAL '4 hours'
        ELSE created_at + INTERVAL '8 hours'
    END as sla_deadline
FROM tickets;

Different service levels get different time windows.

Best for: SLA tracking, priority-based scheduling.

Real-World Example: Calculate Shift Times

Calculate when employee shifts end, including break time:

SELECT
    emp_id,
    first_name,
    shift_start,
    shift_start + INTERVAL '8 hours' as shift_end,
    shift_start + INTERVAL '4 hours' as break_time,
    CASE
        WHEN CURRENT_TIMESTAMP > shift_start + INTERVAL '8 hours'
        THEN 'Shift ended'
        WHEN CURRENT_TIMESTAMP > shift_start + INTERVAL '4 hours'
        THEN 'On break'
        ELSE 'Working'
    END as shift_status
FROM employees
WHERE shift_start IS NOT NULL
ORDER BY emp_id;

Now you can track real-time shift status.

Update Columns with Calculated Times

Store calculated times in your tables:

UPDATE events
SET reminder_time = created_at + INTERVAL '24 hours'
WHERE reminder_time IS NULL
  AND created_at IS NOT NULL;

-- Verify
SELECT event_id, created_at, reminder_time FROM events LIMIT 5;

Performance Notes

Hour addition is extremely fast. PostgreSQL does this at the machine level. Use it freely in SELECT, WHERE, GROUP BY, and ORDER BY clauses without worrying about performance.

Common Interval Patterns

-- Just hours
created_at + INTERVAL '6 hours'
created_at + INTERVAL '24 hours'

-- Hours with minutes
created_at + INTERVAL '2 hours 30 minutes'

-- Days and hours
created_at + INTERVAL '1 day 6 hours'

-- Weeks
created_at + INTERVAL '2 weeks' + INTERVAL '3 hours'

-- Negative (subtracting)
created_at - INTERVAL '3 hours'

-- From variable
created_at + MAKE_INTERVAL(hours => hours_column)

FAQ

Q: How do I add fractional hours (like 1.5 hours)? A: Use decimal in the interval: INTERVAL '1.5 hours' or MAKE_INTERVAL(hours => 1.5).

Q: Can I add hours from a column? A: Yes, use MAKE_INTERVAL: timestamp + MAKE_INTERVAL(hours => column_name).

Q: What about daylight saving time? A: INTERVAL addition doesn’t account for DST. For DST-aware calculations, use AT TIME ZONE. Regular interval math just adds the time duration.

Q: Can I add mixed units? A: Yes: INTERVAL '2 days 6 hours 30 minutes' works fine.

Q: What’s the difference between + INTERVAL ‘24 hours’ vs. + INTERVAL ‘1 day’? A: For most purposes, they’re equivalent. But 1 day respects month lengths and DST changes; 24 hours is always exactly 86400 seconds.

Q: Does adding hours cross month/year boundaries correctly? A: Yes, completely automatic. Jan 31 + 1 day = Feb 1 works perfectly.

Q: Can I add negative intervals? A: Yes: timestamp + INTERVAL '-5 hours' is the same as timestamp - INTERVAL '5 hours'.

Wrapping Up

Adding hours to timestamps in PostgreSQL is simple: use the + operator with INTERVAL 'X hours'. For dynamic hours from columns, use MAKE_INTERVAL(hours => column).

The key takeaway: PostgreSQL handles all the complexity of day boundaries, month lengths, and leap years automatically. Just add and go.