📟
Charl•se

Compute time intervals in PostgreSQL

Calculating differences between dates and datetimes has different flavours 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