How to Calculate Timestamp Difference in PostgreSQL

Problem

You have two columns of the type timestamp and you want to calculate the difference between them.

Example

In the travel table, there are three columns: id, departure, and arrival. You'd like to calculate the difference between the arrival and the departure.

The travel table looks like this:

Solution 1: Difference in Days, Hours, Minutes, and Seconds

SELECT
  id,
  departure,
  arrival,
  arrival - departure AS difference
FROM travel;

The result is:

Discussion

To calculate the difference between the timestamps in PostgreSQL, simply subtract the start timestamp from the end timestamp. Here, it would be arrival - departure. The difference will be of the type interval, which means you'll see it in days, hours, minutes, and seconds.

Solution 2: Difference in Years, Months, Days, Hours, Minutes, and Seconds

SELECT
  id,
  departure,
  arrival,
  AGE(arrival, departure) AS difference
FROM travel;

The result is:

Discussion

If you'd like to get the difference in years, months, days, hours, minutes, and seconds, use the AGE(end, start) function. Here, it would be AGE(arrival, departure). In this solution, the resulting column is also of the type interval.

Solution 3 (difference in seconds)

SELECT
  id,
  departure,
  arrival,
  EXTRACT(EPOCH FROM (arrival - departure)) AS difference
FROM travel;

Discussion

If you'd like to calculate the difference between the timestamps in seconds in PostgreSQL, subtract one date from the other (here: arrival - departure) then extract EPOCH from the difference to get it in seconds. Here's more about the EXTRACT() function.