Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version


PostgreSQL 16.0 (Debian 16.0-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit



SELECT 1


-- get "variable" via CTE
create or replace function remaining_period(start date, months int)
   returns interval 
  language sql 
  immutable strict
as $$
  with remaining(tm) as 
       (select age((start + (months || ' months')::interval)::date, now()::date) as remaining_time)
  select case when tm > interval '0 day' 
              then tm
              else interval '0 day'
         end
    from remaining;
$$;

-- Just use calculation directly
create or replace function remaining_period2(start date, months int)
   returns interval 
  language sql 
  immutable strict
as $$
    select greatest( age((start + (months || ' months')::interval)::date, now()::date)
                   , interval '0 day'
                   ); 
$$;  

CREATE FUNCTION



CREATE FUNCTION


with  test_data (test_date, test_months) as 
      ( values ('2020-01-01'::date, 6)
             , ('2024-01-01'::date, 6) 
             , ('2025-01-01'::date, 6) 
      ) 
select test_date
     , remaining_period(test_date, test_months)
     , remaining_period2(test_date, test_months)
  from test_data;


test_date
remaining_period
remaining_period2


2020-01-01
00:00:00
00:00:00

2024-01-01
4 mons 22 days
4 mons 22 days

2025-01-01
1 year 4 mons 22 days
1 year 4 mons 22 days



SELECT 3

test_date	remaining_period	remaining_period2
2020-01-01	00:00:00	00:00:00
2024-01-01	4 mons 22 days	4 mons 22 days
2025-01-01	1 year 4 mons 22 days	1 year 4 mons 22 days