Skip site navigation (1) Skip section navigation (2)

Re: dynamic interval in plpgsql

From: Thilo Hille <thilo(at)resourcery(dot)de>
To: josh(at)agliodbs(dot)com
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: dynamic interval in plpgsql
Date: 2004-03-30 12:52:25
Message-ID: 40696D89.3080203@resourcery.de (view raw or flat)
Thread:
Lists: pgsql-novice
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.
>
>  
>


In response to

pgsql-novice by date

Next:From: Manfred KoroschetzDate: 2004-03-30 20:59:39
Subject: Equivalent of MSSQL "PATINDEX" ?
Previous:From: Oliver FrommeDate: 2004-03-30 10:38:17
Subject: Re: Images in Database

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group