PostgreSQL: Subtract Hours from Timestamp

I frequently need to look back in time—finding events from the past 24 hours, calculating when something should have been done, or determining time windows for data analysis. Subtracting hours from timestamps is just as common as adding them, and PostgreSQL handles it seamlessly. Whether you’re writing monitoring queries to flag old tickets, analyzing recent events, or calculating SLA windows, subtracting hours is a fundamental operation you’ll use constantly.

The simplicity is deceptive. PostgreSQL automatically handles month and year boundaries, daylight saving time calculations, and all the edge cases that make time manipulation tricky in application code. Trust the database to do the math correctly.

Quick Answer: Use - INTERVAL ‘… hours’

To subtract hours from a timestamp:

SELECT
    created_at,
    created_at - INTERVAL '6 hours' as six_hours_ago,
    created_at - INTERVAL '24 hours' as one_day_ago,
    created_at - INTERVAL '48 hours' as two_days_ago
FROM events
LIMIT 5;

Just use the - operator instead of +.

How It Works

PostgreSQL treats INTERVAL subtraction identically to addition, just going backward in time. Works with day boundaries automatically.

Different Approaches & When to Use Them

Approach 1: Simple Hour Subtraction

SELECT
    event_time - INTERVAL '1 hour' as one_hour_back
FROM events;

Approach 2: With MAKE_INTERVAL

SELECT
    timestamp - MAKE_INTERVAL(hours => hours_column)
FROM table_name;

For dynamic hour values.

Approach 3: Combined with Other Units

SELECT
    deadline - INTERVAL '2 hours 30 minutes' as actual_deadline
FROM tasks;

Real-World Example: SLA Window Calculation

Find when tickets should have been resolved:

SELECT
    ticket_id,
    created_at,
    sla_hours,
    created_at + MAKE_INTERVAL(hours => sla_hours) as deadline,
    CASE
        WHEN CURRENT_TIMESTAMP > (created_at + MAKE_INTERVAL(hours => sla_hours))
        THEN 'SLA Breached'
        ELSE 'Within SLA'
    END as sla_status
FROM tickets;

FAQ

Q: How do I subtract just minutes? A: Use INTERVAL '... minutes' instead of hours.

Q: Can I subtract from both sides of comparison? A: Yes, common pattern for lookback windows.

Q: Difference between subtracting hours vs. using negative hours? A: time - INTERVAL '5 hours' and time + INTERVAL '-5 hours' are identical.

Wrapping Up

Subtract hours using the - operator with INTERVAL. Works identically to addition, just negative direction.