Rails doesn't convert timezone (PostgreSQL)

Question

I have a problem with timezones and a postgresql db (Rails 3.0.4, PostgreSQL 9.0). I'm using a custom scope, in which I append some conditions, do joins etc.pp.

The problem is, that Rails don't convert the times to my local timezone. Here is the code of the scope:

  scope :with_activities_within_range_in_week, lambda{ |latMin, lngMin, latMax, lngMax, date|
    select("date_trunc('day', activities.starting_at) as date,
      count(activities.id) as value
    ") \
    .within(latMin, lngMin, latMax, lngMax) \
    .joins(:activities) \
    .merge(Activity.in_week(date)) \
    .group("date") \
    .order("date")
  }

The within method checks for ranges, the Activity.in_week scope returns this:

where("activities.starting_at >= ? AND activities.starting_at < ?", start, stop)

And in the select statement, I want to trunc the starting_at field to day.

I'm getting the following output for the date field:

2011-04-07 18:48:32
2011-04-02 14:07:20
2011-04-02 14:06:49

Unfortunately it doesn't include the timezone. When I try to access my model through the "normal" Rails functions, it works:

puts Activity.first.starting_at
-> 2011-04-15 06:47:55 +0200

What I'm doing wrong? Hope someone can help!

thx, tux

Answer 1

Your database is storing your timestamps in UTC (as it should). ActiveRecord is making timezone adjustments when it knows that it has a timestamp; so, when you say this:

puts Activity.first.starting_at

AR knows that starting_at is a timestamp so it instantiates the timestamp as an ActiveSupport::TimeWithZone instance and that class applies the timezone adjustment. But, when you say this:

select("date_trunc('day', activities.starting_at) as date ...

AR isn't going to parse the SQL to figure out that date_trunc will return a timestamp, AR doesn't even know what date_trunc means. AR will just see a string coming out of the database and it will hand it to you without interpretation. You are free to feed that string to ActiveSupport::TimeWithZone (or your favorite time handling class) yourself: there's nothing wrong with telling AR things that it does not and cannot know on its own.

Rails is clever but it isn't magic.