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.