# Compute time intervals in PostgreSQL

Calculating differences between dates and datetimes has different flavors in SQL. Let’s just say that PostgreSQL is not the most elegant or well-equipped language for this matter.

## Behaviour for dates and timestamps

Substracting timestamps will give a time interval:

``````WITH d AS (
SELECT '2020-01-01 10:09:00'::timestamp AS start_date,
'2020-05-15 13:37:00'::timestamp AS end_date
)

SELECT end_date - start_date
FROM d

-- Result
-- 135 days 03:28:00
``````

While substracting dates will directly give the time delta in days:

``````WITH d AS (
SELECT '2020-01-01 10:09:00'::date AS start_date,
'2020-05-15 13:37:00'::date AS end_date
)

SELECT end_date - start_date
FROM d

-- Result
-- 135
``````

## Exact interval in seconds

Perhaps the simplest way to calculate a timestamp difference is to get the number of seconds by extracting the Epoch of the time delta. To get the interval in other time units, just divide the result: for example, divide it by 60x60x24 to get the number of days.

``````SELECT EXTRACT('epoch' FROM end_date - start_date)
FROM d

-- Result
-- 11676480
``````

## Interval in days

Depending on the points in time you want to compare being dates or timestamps, there are two options to calculate the interval in days:

``````SELECT -- Option 1: convert to dates and substract
end_date::date - start_date::date AS days_date,
-- Option 2: substract timestamps and extract days
DATE_PART('day', end_date - start_date) AS days_timestamp
FROM d

-- Result
-- days_date   days_timestamp
-- 135         135.0
``````

## Interval in weeks

``````SELECT -- Number of days divided by 7 and truncated
TRUNC(DATE_PART('day', end_date - start_date)/7)
FROM d

-- Result
-- 19.0
``````

## Interval in hours

``````SELECT -- Number of seconds divided by 3600
EXTRACT('epoch' FROM end_date - start_date) / 3600
FROM d

-- Result
-- 3243.0
``````