Rails 3.1：在一段时间内查询Postgres记录 [英] Rails 3.1: Querying Postgres for records within a time range

问题描述

在我的应用程序中，我有一个 Person 模型。每个 Person 具有指定其默认时区的属性 time_zone 。我也有一个事件模型。每个事件有一个 start_time 和 end_time 时间戳，保存在一个Postgres数据库在UTC时间。

In my app I have a Person model. Each Person has an attribute time_zone that specifies their default time zone. I also have an Event model. Each Event has a start_time and end_time timestamp, saved in a Postgres database in UTC time.

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

I need to create a query that finds events for a particular person that fall between midnight of one day and midnight of the next. The @todays_events controller variable hold the results of the query.

我采用这种方法的原因之一是，我可能有其他时区的人员查看一个人的事件列表。我希望他们看到这一天，因为人们会看到这一天，而不是基于他们作为观察员的时区。

Part of the reason that I'm taking this approach is that I may have people from other time zones looking at the list of events for a person. I want them to see the day as the person would see the day and not based on the time zone they are in as an observer.

无论什么原因，我是在 @todays_events的结果集中，仍然会收到一些事件。我的猜测是，我正在将UTC时间戳记与非UTC参数进行比较那些线。一般来说，只有在前一天晚上开始或结束的事件才会显示在今天的查询结果列表中。

For whatever reason, I'm still getting some events from the previous day in my result set for @todays_events. My guess is that I'm comparing a UTC timestamp with a non-UTC parameter, or something along those lines. Generally, only events that begin or end in the evening of the previous day show up on the query result list for today.

现在，我正在设置： / p>

Right now, I'm setting up:

@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")

我应该如何更改这个，以便我保证只收到今天的结果（根据 @ person.time_zone 在 @todays_activities 查询？

How should I change this so that I'm guaranteed only to receive results from today (per the @person.time_zone in the @todays_activities query?

推荐答案

你输了跟踪你的时区，当你打电话 to_date 所以不要这样做：

You're losing track of your timezones when you call to_date so don't do that:

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

当您 some_date.to_datetime 时，您会收到一个UTC的DateTime实例，因此结果如下： p>

When you some_date.to_datetime, you get a DateTime instance that is in UTC so the result of something like this:

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

将有一个00:00:00的时间和UTC的时区; 00:00:00是 @ person.time_zone 中的正确时间，但不适用于UTC（除非当然 @

will have a time-of-day of 00:00:00 and a time zone of UTC; the 00:00:00 is the correct time-of-day in @person.time_zone but not right for UTC (unless, of course, @person is in in the +0 time zone).

您可以使用 重叠 ：

And you could simplify your query with overlaps:

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

请注意， 重叠以半开间隔工作：

Note that overlaps works with half-open intervals:

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

Each time period is considered to represent the half-open interval start <= time < end, unless start and end are equal in which case it represents that single time instant.

这篇关于Rails 3.1：在一段时间内查询Postgres记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！