More than 24 hours in a day in postgreSQL

You have to adjust interval with justify_interval() function:

select customer_id, justify_interval(avg(return_date - rental_date)) as "avg"
from rental
group by customer_id
order by "avg" DESC;

See official doc:

Adjust interval using justify_days and justify_hours, with additional sign adjustments

Still, it does not explains why the result of the operation is that weird without using justify_interval() (in other words, why we have to apply this function)

Note: thanks to @a_horse_with_no_name for their comment

This is an attempt to explain the behavior.

PostgreSQL intervals don't get “justified” more than necessary during interval arithmetic. I'd say that has two reasons:

  • speed
  • loss of exactness (e.g. when days are being converted by months, assuming a month to have 30 days

So you get results like this:

SELECT INTERVAL '1 day 20 hours' + INTERVAL '5 days 30 hours';

 6 days 50:00:00
(1 row)

The same holds for division:

SELECT INTERVAL '6 days 50 hours' / 2;

 3 days 25:00:00
(1 row)

Always adjusting the hours to be less than 24 would make a long calculation like the avg you are calculating unnecessarily complicated, and as you have found, there are functions to adjust the result.