# How to Calculate Timestamp Difference in PostgreSQL

• 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.