Обсуждение: 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. > > >