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