Re: dynamic interval in plpgsql

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.
>
>
>