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.

CodePudding user response：

First adjust the two dates to be the first day of the first full month and the last day of the last full month plus one day (t CTE) and then calculate the age between the two adjusted dates. Here is an illustration using sample_data CTE.

with sample_data(d1, d2) as (values
('2022-01-03'::date,'2022-03-04'::date),('2022-01-01','2022-05-30'),('2022-01-31','2022-05-31')),

t as
( 
 select 
  case when d1 = date_trunc('month', d1) 
       then d1 
       else date_trunc('month', d1)   interval '1 month' end::date m1,
  case when d2 = date_trunc('month', d2)   interval '1 month - 1 day' 
       then d2   interval '1 day'
       else date_trunc('month', d2) end::date m2
 from sample_data
) 
select m1, m2, age(m2, m1) from t;

CodePudding user response：

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.