Re: Select Interval in plpgsql

On Mar 21, 2005, at 10:24 PM, Derrick Betts wrote:

> I can't seem to figure out how to accomplish this task in a plpgsql
> function:
>  
> I have a timestamp variable 'apt_time' and a varchar variable
> 'time_offset'.  The time_offset variable usually looks like this: '-2
> hours'.  What I want to do is add the time_offset to the timestamp. 
> So I have tried:
>  

Here is an example:

create or replace function time_test (time_offset text)
returns timestamp as '
declare
tm timestamp = now();
adjust timestamp;
begin
adjust := tm + time_offset::interval;
return adjust;
end;
' language plpgsql;

select time_test('2 hours');
time_test
----------------------------
2005-03-22 02:45:59.357986
(1 row)

You could also write the assignment like this:

select into adjust tm + time_offset::interval;

John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL