How to get the full calendar months between two dates in PostgreSQL

Question

I have two dates, e.g. '2022-01-03' and '2022-03-04', is there any neat way to calculate ONLY the completed full calendar months between these dates?

Some examples with their requested outputs:

'2022-01-03' and '2022-03-04'

full calendar months = 1 since only February was a full calendar month between this timespan.

'2022-01-01' and '2022-05-30'

full calendar months = 4 since May has 31 days total.

'2022-01-31' and '2022-05-31'

full calendar months = 3 since the month of May is not completed.

I tried subtracting the dates but it gives me the days difference between these dates. I also tried the function AGE() but it is based also in the days difference, since it is using days to calculate years months etc.

Answer 1

You can generate a full range of days between the two dates as complete months, and then left join all the days only between the original dates. This way, you can check if any months are missing days. The query below is formatted as a function for ease of use:

create function calendar_months(a timestamp, b timestamp) 
returns int as
$$
declare
 total int;
begin
  select sum(case when t1.rec = t1.total then 1 else 0 end) into total 
  from (select extract(year from t), extract(month from t), 
    sum(case when t1 is not null then 1 else 0 end) rec, count(*) total 
    from generate_series(date_trunc('month', a), date_trunc('month', b) + interval '1 month' - interval '1 day', interval '1 day') t 
    left join generate_series(a, b - interval '1 day', interval '1 day') t1 on t = t1 
    group by extract(year from t), extract(month from t)) t1;
  return total;
end;
$$ language plpgsql;

See fiddle.