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