Postgres Pro
Downloads
Home   >   mailing lists

Thread: Select Interval in plpgsql

Select Interval in plpgsql

From
"Derrick Betts"
Date:
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:
 
SELECT apt_time + interval time_offset INTO Adjusted_Time; 
 
Where Adjusted_Time is the variable to store the result.
 
But I keep getting errors.  I'm not sure where the problem is, as I obviously have not set up the field or variable types properly, or have structured the SELECT improperly.
 
This statement works just fine:
 
SELECT apt_time + interval '-2 hours' INTO Adjusted_Time; 
 
but when I try to use a variable with the interval, I can't get it to work.
 
Any help would be appreciated.
 
Thanks,
Derrick
 
 

Re: Select Interval in plpgsql

From
John DeSoi
Date:
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