Rails does not convert timezone (PostgreSQL)

Question

I have a problem with time zones and postgresql db (Rails 3.0.4, PostgreSQL 9.0). I use a custom scope in which I add some conditions, joins, etc.

.

The problem is that Rails does not convert time to my local time zone. Here is the area code:

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 range is checked inside the method, the Activity.in_week area returns this:

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

And in the select clause, I want to collapse the start_at field by day.

I get 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 does not include the time zone. When I try to access my model through the "regular" Rails functions, it works:

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

What am I doing wrong? Hope someone can help!

THX, Tux

Answer 1

Your database stores your timestamps in UTC (as it should be). ActiveRecord adjusts the time zone 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 creates an instance of timestamp as an instance of ActiveSupport::TimeWithZone , and this class applies the time zone adjustment. But when you say it:

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

AR is not going to parse SQL to find out that date_trunc will return a timestamp; AR doesn't even know what date_trunc means. AR will simply see the row exiting the database and pass it to you without interpretation. You can feed this line ActiveSupport::TimeWithZone before ActiveSupport::TimeWithZone (or your favorite time-handling class): there is nothing wrong with telling AR things that aren't there and they can't know on their own.

The rails are smart, but it's not magic.