CODEWITHSUNDEEP
sqlpostgresqlrelational-database
tealoque
tealoque

Reputation: 3

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

I have a preexisting 'events' table that includes an event_datetime column and an event_duration_minutes column. The two columns are datetime and integer, respectively.

I've recently come across the need to trigger an email when an event ends. In order to accomplish this, I am creating a recurring job that sweeps the DB every 10 minutes looking for recently completed events. I thus started writing a query to find all events that have ended within a given time window, but due to the variable nature of the duration field for each record, the query has been eluding me.

I am thinking that the best approach is to add in an end_time column and just automatically set it to start_time + duration. Is that the right way to go?

In any case, I am drawing a complete blank on how to go about the query - is there a simple way to approach these cases? Or is the general need for such a query a sign that your DB needs some work?

EDIT - This is on postgres 9.2. And here is what the the schema looks like in my rails schema.rb:

create_table "events", :force => true do |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

Upvotes: 0

Views: 271

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 324771

The usual solution to this is to create an expression index on the calculated end time. You can then do indexed searches for it easily.

The simplest way to do this is to define a simple SQL function that does the calculation then use it in both your expression index and queries you want to use the index.

eg:

CREATE OR REPLACE FUNCTION startduration_to_end(starttime timestamp, duration_minutes integer) RETURNS timestamp AS $$
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 another on the start time and the end, depending on query patterns, but keep the cost of maintaining the indexes in mind if your tables have a 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 by using your startduration_to_end function, eg:

SELECT *
FROM mytable t
WHERE startduration_to_end(t.event_datetime,t.event_duration_minutes) BETWEEN current_timestamp - INTERVAL '10' MINUTE AND current_timestamp;

Because of variation in execution times, etc, you should really be scanning enough of the index that your searches overlap, or searching for everything newer than the exact timestamp of the last search instead of the last 10 minutes.

Upvotes: 2

Related Questions