Thread: another problem with pgsql and interva/timestamp

Hi posgresql gurus!

As you see, I have a LOT of problems with interval and timestamp
types...

Now, I'm trying to add a numer of days to a date.

If I do this:
select now() + interval '2 day'

I get a datetime two days in the future, thats ok. I want to do this,
but I have these "2" into a variable inside pl/pgsql.

And, when I try to do this:
(N_cantidad_dias is numeric, D_Fecha_hasta is date, v_aux is varchar)

N_cant_dias = 2;
v_aux = to_char(N_cantidad_dias,''999'')||'' day'';
D_Fecha_hasta := now()+ vaux::interval;

I get an error: Cannot cast type character varying to interval.

Then, I try in anoter way:

N_cant_dias = 2;
v_aux = to_char(N_cantidad_dias,''999'')||'' day'';
select into D_fecha_hasta now() + ''''v_aux''''::interval

but I get this error messages:
WARNING:  line 110 at select into variables
ERROR:  parser: parse error at or near "$1" at character 20

Really, I cant detect what is wrong here...

Thanks in advance!

--
Fernando O. Papa
DBA

"Fernando Papa" <fpapa@claxson.com> writes:
> Now, I'm trying to add a numer of days to a date.

> If I do this:
> select now() + interval '2 day'

Do you actually want a datetime result, or just a date?  If the desired
result is a date, you'd find it a lot easier to use the
date-plus-integer operator:

regression=# select current_date;
    date
------------
 2003-03-21
(1 row)

regression=# select current_date + 4;
  ?column?
------------
 2003-03-25
(1 row)

If you do really need sub-day resolution, then stick to timestamp plus
interval.

> I get a datetime two days in the future, thats ok. I want to do this,
> but I have these "2" into a variable inside pl/pgsql.

If you've got a numeric variable, the easiest way to convert it to an
interval is to use the float-times-interval operator:

regression=# select 33 * interval '1 day';
 ?column?
----------
 33 days
(1 row)

regression=# select now() + 33 * interval '1 day';
           ?column?
-------------------------------
 2003-04-23 15:29:12.592024-04
(1 row)

No need to fool with insertion of text into an interval literal ...

            regards, tom lane

Thanks again Tom...
select now() + 33 * interval '1 day';

works perfect!

--
Fernando O. Papa
DBA


> -----Mensaje original-----
> De: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Enviado el: viernes, 21 de marzo de 2003 16:31
> Para: Fernando Papa
> CC: pgsql-general@postgresql.org
> Asunto: Re: [GENERAL] another problem with pgsql and
> interva/timestamp
>
>
> "Fernando Papa" <fpapa@claxson.com> writes:
> > Now, I'm trying to add a numer of days to a date.
>
> > If I do this:
> > select now() + interval '2 day'
>
> Do you actually want a datetime result, or just a date?  If
> the desired result is a date, you'd find it a lot easier to
> use the date-plus-integer operator:
>
> regression=# select current_date;
>     date
> ------------
>  2003-03-21
> (1 row)
>
> regression=# select current_date + 4;
>   ?column?
> ------------
>  2003-03-25
> (1 row)
>
> If you do really need sub-day resolution, then stick to
> timestamp plus interval.
>
> > I get a datetime two days in the future, thats ok. I want
> to do this,
> > but I have these "2" into a variable inside pl/pgsql.
>
> If you've got a numeric variable, the easiest way to convert
> it to an interval is to use the float-times-interval operator:
>
> regression=# select 33 * interval '1 day';
>  ?column?
> ----------
>  33 days
> (1 row)
>
> regression=# select now() + 33 * interval '1 day';
>            ?column?
> -------------------------------
>  2003-04-23 15:29:12.592024-04
> (1 row)
>
> No need to fool with insertion of text into an interval literal ...
>
>             regards, tom lane
>