跟踪包含"start_time"和"持续时间"列的表的"end_time"值的最佳方法

Question

我有一个预先存在的'events'表，其中包括event_datetime列和event_duration_minutes列。这两列分别是datetime和integer。

我最近发现需要在事件结束时触发电子邮件。为了实现这一点，我创建了一个循环作业，它每10分钟扫描一次DB，查找最近完成的事件。因此，我开始编写一个查询来查找在给定时间窗口内结束的所有事件，但是由于每个记录的duration字段的可变性质，查询一直在逃避我。

我认为最好的方法是添加一个end_time列，并自动将其设置为start_time + duration。这是正确的方法吗?

在任何情况下，我画了一个完全空白关于如何去查询-有一个简单的方法来处理这些情况?或者，对此类查询的一般需求是否表明您的数据库需要进行一些工作?

编辑-这是在postgres 9.2上。下面是我的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

Answer 1

通常的解决方案是在计算的结束时间上创建一个表达式索引。然后你可以很容易地对它进行索引搜索。

最简单的方法是定义一个简单的SQL函数来进行计算，然后在你的表达式索引和你想要使用索引的查询中使用它。

,

CREATE OR REPLACE FUNCTION startduration_to_end(starttime timestamp, duration_minutes integer) RETURNS timestamp AS $$
SELECT $1 + $2 * INTERVAL '1' MINUTE';
$$ LANGUAGE sql;

:

CREATE INDEX mytable_end_index ON mytable 
( (startduration_to_end(event_datetime,event_duration_minutes ) );

根据查询模式，您可能需要另一个开始时间和结束时间，但是如果您的表具有高变化率，请记住维护索引的成本:

CREATE INDEX mytable_end_index ON mytable 
(event_datetime, (startduration_to_end(event_datetime,event_duration_minutes ) );

您可以使用startduration_to_end函数来查询这些索引，例如:

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

由于执行时间的变化等原因，您应该扫描足够多的搜索重叠的索引，或者搜索比上次搜索的确切时间戳更新的所有内容，而不是最近10分钟。