PostgreSQL: sum of days in range

I have a table as follows:

                   Table "public.fish_schedule"
 Column |          Type          | Collation | Nullable | Default 
--------+------------------------+-----------+----------+---------
 name   | character varying(255) |           | not null | 
 tr     | timerange              |           | not null | 
 mr     | int4range              |           | not null | 

Example data:

     name     |         tr          |   mr   
--------------+---------------------+--------
 ray          | [04:00:00,21:00:00) | [8,12)
 moray eel    | [00:00:00,24:00:00) | [8,11)
 yellow perch | [00:00:00,24:00:00) | [1,4)
(3 rows)

The field mr represents the month range. I would like to add the total number of days in the range of, for example, moray eel, which would be from August to October.

I’ve only managed to get the following SQL working so far, and haven’t got the faintest idea how to write a function to do what I need.

SELECT generate_series(1,12) AS n, 
       generate_series('2020-01-01'::date,'2020-12-01'::date,'1 month'::interval)+ '1 month'::interval - generate_series('2020-01-01'::date,'2020-12-01'::date,'1 month'::interval) as m;

Here’s the output.

 n  |    m    
----+---------
  1 | 31 days
  2 | 29 days
  3 | 31 days
  4 | 30 days
  5 | 31 days
  6 | 30 days
  7 | 31 days
  8 | 31 days
  9 | 30 days
 10 | 31 days
 11 | 30 days
 12 | 31 days
(12 rows)

So, the function would have to add up the days in August(31), September(30), and October(31), based on the range in the mr field.

Would appreciate any guidance or pointers.

UPDATE: Here is the solution for the curious.

WITH feeding(name, the_hours, start_schedule, end_schedule) AS
(SELECT name,
EXTRACT(HOUR FROM upper(tr)-lower(tr)),
make_date(extract(year from now())::int4,lower(mr),1)::timestamp,
make_date(extract(year from now())::int4,upper(mr)-1,1)::timestamp
+ interval '1 month' - interval '1 day'
from fish_schedule
)
SELECT name, SUM(the_hours * (EXTRACT (days from (end_schedule - start_schedule)) + 1)) "total_hours"
FROM feeding
GROUP by name
ORDER by total_hours;

Answer

You do not need the number of days in a month since your for any given row your time frame in contiguous. The following converts the MT column to timestamps from the 1st day of lower value of range the last date of the upper value of the range in a CTE. The main part the then extracts number of days from the difference.

with feeding( name, start_schedule, end_schedule) as 
     ( select name 
            , make_date(extract(year from now())::int4,lower(mr),1)::timestamp
            , make_date(extract(year from now())::int4,upper(mr),1)::timestamp
              + interval '1 month' - interval '1 day'
         from fish_schedule
     )
select name, extract(days from (end_schedule - start_schedule)) + 1 "# of days" 
  from feeding; 

Note: A slight matter of opinion here. The “interval ‘1 day’ and the addition of 1 in the main can be eliminated and still produce the same result. IMO the above more clearly the intent. But not having them makes the query slightly shorter and ‘infinitesimally’ faster.

PS. It also handles @Vesa point on leap year.