Обсуждение: 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 >