Rails 3.1:在Postgres中查询时间段内的记录

Question

在我的应用程序我有一个Person模型。每个Person都有一个属性time_zone，用来指定它们的默认时区。我也有一个Event模型。每个Event都有start_time和end_time时间戳，以UTC时间保存在Postgres数据库中。

我需要创建一个查询，该查询查找某个人在某一天午夜和下一天午夜之间的事件。@todays_events控制器变量保存查询的结果。

我采用这种方法的部分原因是，我可能会让来自其他时区的人查看一个人的事件列表。我希望他们能像人一样看待这一天，而不是基于他们作为观察者所处的时区。

无论出于何种原因，我仍然从前一天的@todays_events.结果集中获得一些事件。我的猜测是，我正在将UTC时间戳与非UTC参数进行比较，或者沿着这些行进行比较。一般来说，只有在前一天晚上开始或结束的事件才会显示在今天的查询结果列表中。

现在，我正在设置:

@today    = Time.now.in_time_zone(@person.time_zone).midnight.to_date
@tomorrow = (@today + 1.day ).to_datetime
@today    = @today.to_datetime

我的查询如下:

@todays_activities = @person.marks.where("(start_time >= ? AND start_time < ?) OR (end_time >= ? AND end_time < ?);", @today, @tomorrow, @today, @tomorrow ).order("start_time DESC")

我应该如何改变这一点，以便我保证只从今天收到结果(根据@todays_activities查询中的@person.time_zone) ?

Answer 1

当你调用to_date时，你会丢失你的时区，所以不要这样做:

@today    = Time.now.in_time_zone(@person.time_zone).midnight.utc
@tomorrow = @today + 1.day

当你some_date.to_datetime时，你会得到一个UTC的DateTime实例，所以结果是这样的:

Time.now.in_time_zone(@person.time_zone).midnight.to_date.to_datetime

的时间为00:00:00，时区为UTC;00:00:00是@person.time_zone中正确的时间，但不适合UTC(当然，除非@person在+0时区)。

您可以使用overlaps:

简化您的查询

where(
    '(start_time, end_time) overlaps (timestamp :today, timestamp :tomorrow)',
    :today => @today, :tomorrow => @tomorrow
)

注意overlaps工作在半开间隔:

每个时间段被认为代表半开区间start <= time < end，除非开始和结束相等，在这种情况下它代表单个时间瞬间。