Thread: dynamic interval in plpgsql

hi,
i work on a stored procedure which does some timespecific calculations
in plpgsql.
in a loop i want  to increase a timestamp by a changing interval. but i
found no way to assign a variable to INTERVAL .
finally i used plpython for the function but i still wonder if it could
be done with plpgsql?

regards thilo

Thilo,

> i work on a stored procedure which does some timespecific calculations
> in plpgsql.
> in a loop i want  to increase a timestamp by a changing interval. but i
> found no way to assign a variable to INTERVAL .
> finally i used plpython for the function but i still wonder if it could
> be done with plpgsql?

I'm sure this is possible, but from your description I can't figure out what
you're trying to do.  Please be more explicit.

--
-Josh Berkus
 Aglio Database Solutions
 San Francisco

On Mon, Mar 29, 2004 at 12:51:11PM +0200, Thilo Hille wrote:
> hi,
> i work on a stored procedure which does some timespecific calculations
> in plpgsql.
> in a loop i want  to increase a timestamp by a changing interval. but i
> found no way to assign a variable to INTERVAL .
> finally i used plpython for the function but i still wonder if it could
> be done with plpgsql?
>
> regards thilo

You can do that with some select statements, non?
run the following and hope it helps
------------------------------------
create sequence test_interval_id_seq;
create table test_interval (
id integer UNIQUE DEFAULT nextval('test_interval_id_seq'),
formulation varchar(100),
interval_col interval,
check (interval_col >= '0 day'::interval)
);
insert into test_interval (formulation,interval_col) values ('1 day','1 day');
insert into test_interval (formulation, interval_col) values ('timestamp ''today'' - timestamp ''tomorrow''',timestamp
'today'-timestamp 'tomorrow'); 
insert into test_interval (formulation, interval_col) values (
'timestamp ''today''- timestamp ''yesterday''',
timestamp 'today'- timestamp 'yesterday');
insert into test_interval (formulation, interval_col) values (
'timestamp ''tomorrow''- timestamp ''yesterday''',
timestamp 'tomorrow'- timestamp 'yesterday');
insert into test_interval (formulation, interval_col) values (
'now() - timestamp ''yesterday''',
now() - timestamp 'yesterday');
insert into test_interval (formulation, interval_col) values (
'timestamp ''today'' + interval ''1 month 04:01''',
timestamp 'today' + interval '1 month 04:01');
select * from test_interval;
drop table test_interval;
drop sequence test_interval_id_seq;





--
joe speigle
www.sirfsup.com

Hello Josh,

lets take a table of the following structure:

table events:
event string |  start timestamp | duration int (interval in seconds)
"event1"  |  '2004-03-30 08:00:00' | 7200
"event1"  |  '2004-03-30 13:00:00' | 32400

now i want to do some dataprocessing weighting how long an event was
occurring in a different time of  the day.
lets say
time1= 00:00:00 - 09:00:00   weight: seconds*1
time2= 12:00:00 - 18:00:00   weight: seconds*2
time3= 20:00:00 - 23:00:00   weight: seconds*3

for instance:
"event1" should return  (3600*1) as it overlaps with time1 for 1 hour.
"event2" should return  (18000*1)+(7200*2) as it overlaps with time1 for
5 hour and time2 for 2 hours.

my idea was something like this:
(this is notworking code! its just to give the idea, i trashed my
orginal attempts accidentally)

create function eventweight(timestamp,int) return int as'
 event_start=$1
 event_end=interval '$2 seconds'          // how can i do this with plpgsql?
 day_start=date_trunc('day',$1)
  time_start[0]=daystart +'00:00:00'::time
  time_end[0]=daystart +'09:00:00'::time
  time_weight[0]=1
  time_start[1]=daystart +'12:00:00'::time
  time_end[1]=daystart +'18:00:00'::time
  time_weight[1]=2
  time_start[2]=daystart +'20:00:00'::time
  time_end[2]=daystart +'23:00:00'::time
  time_weight[1]=3
  current=0
  while current<size(time_start)
       if  time_start[current]<=event_start && time_end[current]>=event_end
          ret+=extract(epoche from
(time_end[current]-time_start[current])::timestamp)::int*time_weight[current]
      elseif  //check & calculate fractions.....
           ...
      fi
 elihw
 return ret
' language 'menonothin'

i made a similiar function with plpython. i converted the timestamp to
epoche to get them into python as float.
but know i have some timezone issues. thats why i thought this would be
best done with plpgsql in the first place :(
what i was not getting: "event_end=interval '$2 seconds'" i tried
several attempts with different quotes but  i didnt find out howto
assign a variable instead of a fixed string to the INTERVAL command
using plpgsql.

thanks & regards thilo

Josh Berkus wrote:

>Thilo,
>
>
>
>>i work on a stored procedure which does some timespecific calculations
>>in plpgsql.
>>in a loop i want  to increase a timestamp by a changing interval. but i
>>found no way to assign a variable to INTERVAL .
>>finally i used plpython for the function but i still wonder if it could
>>be done with plpgsql?
>>
>>
>
>I'm sure this is possible, but from your description I can't figure out what
>you're trying to do.  Please be more explicit.
>
>
>