skip to Main Content

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.

2

Answers


  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.

    Login or Signup to reply.
  2. 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;
    
    m1 m2 age
    2022-02-01 2022-03-01 1 mon
    2022-01-01 2022-05-01 4 mons
    2022-02-01 2022-06-01 4 mons
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search