sql - Best approach for tracking 'end_time' value for a table with 'start_time' and 'duration' columns -

i have preexisting 'events' table includes event_datetime column , event_duration_minutes column. 2 columns datetime , integer, respectively.

i've come across need trigger email when event ends. in order accomplish this, creating recurring job sweeps db every 10 minutes looking completed events. started writing query find events have ended within given time window, due variable nature of duration field each record, query has been eluding me.

i thinking best approach add in end_time column , automatically set start_time + duration. right way go?

in case, drawing complete blank on how go query - there simple way approach these cases? or general need such query sign db needs work?

edit - on postgres 9.2. , here the schema looks in rails schema.rb:

create_table "events", :force => true |t|   t.string   "title"   t.text     "details"   t.datetime "created_at",             :null => false   t.datetime "updated_at",             :null => false   t.datetime "event_datetime"   t.integer  "instructor_id"   t.integer  "event_duration_minutes"   t.datetime "started_at_time" end 

the usual solution create expression index on calculated end time. can indexed searches easily.

the simplest way define simple sql function calculation use in both expression index , queries want use index.

eg:

create or replace function startduration_to_end(starttime timestamp, duration_minutes integer) returns timestamp $$ select $1 + $2 * interval '1' minute'; $$ language sql; 

then:

create index mytable_end_index on mytable  ( (startduration_to_end(event_datetime,event_duration_minutes ) ); 

you might want on start time , end, depending on query patterns, keep cost of maintaining indexes in mind if tables have high change rate:

create index mytable_end_index on mytable  (event_datetime, (startduration_to_end(event_datetime,event_duration_minutes ) ); 

you can query using these indexes using startduration_to_end function, eg:

select * mytable t startduration_to_end(t.event_datetime,t.event_duration_minutes) between current_timestamp - interval '10' minute , current_timestamp; 

because of variation in execution times, etc, should scanning enough of index searches overlap, or searching newer exact timestamp of last search instead of last 10 minutes.