Compute time intervals in PostgreSQL

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