1

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?

mu is too short
  • 426,620
  • 70
  • 833
  • 800
Clay
  • 2,949
  • 3
  • 38
  • 54
  • Note: I'm storing the whole time zone string in the `@person.time_zone` field, not just the offset. – Clay Dec 11 '11 at 19:47
  • One of the offending events has the following in the db table for `start_time`: `2011-12-10 23:28:00` and `end_time`: `2011-12-11 03:00:00` with `time_zone` of `Eastern Time (US & Canada)`. – Clay Dec 11 '11 at 19:52

1 Answers1

10

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.

mu is too short
  • 426,620
  • 70
  • 833
  • 800
  • Awesome! This is the single best explanation of this that I have seen after a lot of searching. I didn't know about overlaps, either Thank you very much! Time zones are frustrating! – Clay Dec 11 '11 at 23:37
  • With `overlaps` in this context, using your code as shown, I think that `@today` and `@tomorrow` need to be cast. This error is thrown: `PGError: ERROR: function pg_catalog.overlaps(timestamp without time zone, timestamp without time zone, unknown, unknown) is not unique LINE 1: ...arks"."person_id" = 1 AND ((start_time, end_time) overlaps (... ^ HINT: Could not choose a best candidate function. You might need to add explicit type casts.` – Clay Dec 12 '11 at 00:32
  • 1
    @Clay: Try adding some casts: `overlaps (:today::timestamp, :tomorrow:timestamp)` or `overlaps (timestamp :today, timestamp :tomorrow)`. And for the record, I hate timezones too, almost as much as I hate DST. – mu is too short Dec 12 '11 at 00:48