Say, I have two dates:

start_date = '2018-06-01'
end_date = '2022-10-01'

How can I get a table of months and the number of times they occur between the two dates?

I want an output like the following:

month | count
-------------
1     | 4
2     | 4
3     | 4
4     | 4
5     | 4
6     | 5
7     | 5
8     | 5
9     | 5
10    | 5
11    | 4
12    | 4

Edit (to answer questions in comments):

1. No, I don't have table with dates, If I had, I would have added that in question
2. Year is not important, If it was required, I would have mentioned
3. Inclusive, and that is why I had given example input and output to tell exactly what I want.

CodePudding user response：

demo:db<>fiddle

SELECT
    date_part('month', gs),  -- 2
    COUNT(*)                 -- 3
FROM generate_series(        -- 1
   '2018-06-01', 
   '2022-10-01', 
   interval '1 month'
) gs
GROUP BY 1                   -- 3

1. Generate all months between the given dates. Here the given dates are INCLUDED. If you want to exclude them, you need to add/subtract one month like:

   generate_series(
       '2018-06-01'   interval '1 month', 
       '2022-10-01' - interval '1 month', 
       interval '1 month'
   )
   

2. Extract the month component of the generated dates

3. Group and count them.