Pass 'interval' value to date_trunc function in PostgreSQL procedure

Question

I am fetching the "age" of two timestamp columns (End and Start) and further choosing it as to_char 'MM' format. So I have the difference of the months from two timestamp columns.

The syntax in the above statement goes like :

i = (select to_char(age(End,Start),'MM'));
interval_value = (select i || ' month'); 

Also tried: interval_value = i || ' Month'

Now,

Passing the value of 'i' in another function date_trunc of PostgreSQL.

xyz = (select date_trunc('month',Start::date) + interval_value::text::interval);

The data types for the above variables are:

i numeric :=0
xyz date;
interval_value varchar;

But it doesn't seem to work.

Is there any alternate approach I could use here. The main idea is to get the difference from two timestamps in months and then further passing the difference into the date_trunc function.

Answer 1

I am not entirely sure about your objective, but what you show works with legal identifiers. "end" is a reserved word and I wouldn't use "start" either. Reserved words require double-quoting, best avoided to begin with:

DO
$$
DECLARE
 ts_start timestamp := '2019-03-20 00:00:00'; -- legal name
 ts_end timestamp := '2019-07-02 01:12:13';
 i numeric := (select to_char(age(ts_end, ts_start),'MM'));
 interval_value varchar := i || ' month'; 
 xyz date := (select date_trunc('month',ts_start::date)
 + interval_value::text::interval);
BEGIN
 RAISE NOTICE '%', xyz;
END
$$;

Can be much simpler, though:

DO
$$
DECLARE
 ts_start timestamp := '2019-03-20 00:00:00';
 ts_end timestamp := '2019-06-29 01:12:13';
 xyz date := date_trunc('month', ts_start)
 + date_trunc('month', age(ts_end, ts_start));
BEGIN
 RAISE NOTICE '%', xyz;
END
$$;

db<>fiddle here (raising exceptions to display the result)