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
Show quoted text
-----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
Import Notes
Resolved by subject fallback