lets take a table of the following structure:
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.
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
"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_end=interval '$2 seconds' // how can i do this with plpgsql?
if time_start[current]<=event_start && time_end[current]>=event_end
elseif //check & calculate fractions.....
' 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
thanks & regards thilo
Josh Berkus wrote:
>>i work on a stored procedure which does some timespecific calculations
>>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 Koroschetz||Date: 2004-03-30 20:59:39|
|Subject: Equivalent of MSSQL "PATINDEX" ?|
|Previous:||From: Oliver Fromme||Date: 2004-03-30 10:38:17|
|Subject: Re: Images in Database|