title: extract days of daterange grouped by month postresql
body: <p>I have a pickupDate and returnDate in my OrderHistory table. I want to extract the sum of rental days of all OrderHistory entries, grouped/ordered by month. A cte seems to be the solution but I don´t get how to implement it in my query since the cte´s i saw were refering to themselves where it says &quot;FROM cte&quot;.</p>
  <p>I tried something like this:</p>
  <pre><code>SELECT
     SUM((EXTRACT (DAY FROM(&quot;OrderHistory&quot;.&quot;returnDate&quot;)-(&quot;OrderHistory&quot;.&quot;pickupDate&quot;)))) as traveltime
    , to_char(&quot;OrderHistory&quot;.&quot;pickupDate&quot;::date, 'YYYY-MM') as M  
    
  FROM
    &quot;OrderHistory&quot; 
  
    GROUP BY 
    M
    
  ORDER BY 
    M
  </code></pre>
  <p>But the outcome doesn´t split bookings btw two months (e.g. pickupDate=27th march 2022 and returnDate=03rd of april 2022) but will assign the whole 7 days to the month of march, since the returndate is in it. It should show 4 days in march and 3 in april.</p>
  <p>Sorry for the probably very stupid question but I am a beginner. (my code is written in postgresql btw)</p>