0

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.

0

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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.