Articles Cookbook
Back to list
PostgreSQL

How to Calculate the Difference Between Two Timestamps in PostgreSQL

Database:

Operators:

AGE(), EXTRACT(), EPOCH

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:

iddeparturearrival
12018-03-25 12:00:002018-04-05 07:30:00
22019-09-12 15:50:002019-10-23 10:30:30
32018-07-14 16:15:002018-07-14 20:40:30
42018-01-05 08:35:002019-01-08 14:00:00

Solution 1 (difference in days, hours, minutes, and seconds):

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

The result is:

iddeparturearrivaldifference
12018-03-25 12:00:002018-04-05 07:30:0010 days 19:30:00
22019-09-12 15:50:002019-10-23 10:30:3040 days 18:40:30
32018-07-14 16:15:002018-07-14 20:40:3004:25:30
42018-01-05 08:35:002019-01-08 14:00:00368 days 05:25:00

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:

iddeparturearrivaldifference
12018-03-25 12:00:002018-04-05 07:30:0010 days 19:30:00
22019-09-12 15:50:002019-10-23 10:30:301 mon 10 days 18:40:30
32018-07-14 16:15:002018-07-14 20:40:3004:25:30
42018-01-05 08:35:002019-01-08 14:00:001 year 3 days 05:25:00

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;
iddeparturearrivaldifference
12018-03-25 12:00:002018-04-05 07:30:00934200
22019-09-12 15:50:002019-10-23 10:30:303523230
32018-07-14 16:15:002018-07-14 20:40:3015930
42018-01-05 08:35:002019-01-08 14:00:0031814700

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.

Recommended courses:

Recommended articles:

See also:

go to top