Rails 3.1: Querying Postgres for records within a

Question

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.

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.

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.

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

My query looks like:

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

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?

Answer 1

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

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

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:

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.