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

What type is activities.starting_at inside the database? And what happens if you select starting_at from activities from within psql? — mu is too short
– mu is too short, Commented Apr 20, 2011 at 23:42
The activities.starting_at is a timestamp without a timezone (stored in utc). select starting_at from activities returns 2011-04-15 04:47:34, also without a timezone. But shouldn't rails convert the time to the specific timezone on the fly? With my example from my question puts Activity.first.starting_at -> 2011-04-15 06:47:55 +0200 it works. — 23tux
– 23tux, Commented Apr 21, 2011 at 7:52
I found another interesting thing: When I try iterate over the results, and dump the class of my date field, I get String. But it should be ActiveSupport::TimeWithZone. I read the documentation of postgresql, and the date_trunc('day', activities.starting_at) should return an timestamp. Why doesn't Rails convert this? — 23tux
– 23tux, Commented Apr 21, 2011 at 8:07
I cover the class issue below, I was writing my answer while you were making your latest comment. — mu is too short
– mu is too short, Commented Apr 21, 2011 at 8:14

mu is too short · Accepted Answer · 2011-04-21 08:13:26Z

5

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.

answered Apr 21, 2011 at 8:13

mu is too short

436k71 gold badges863 silver badges822 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

23tux Over a year ago

thx for your answer. That was it that I figure out by dumping the class. How would you feed the string to `ActiveSupport::TimeWithZone``? Iterate through the results and convert the string?

mu is too short Over a year ago

You'll be iterating through the results anyway so you could throw the TimeWithZone stuff in there. I'm not an AR3 scope guru though so there might be a better way.

Collectives™ on Stack Overflow

Rails doesn't convert timezone (PostgreSQL)

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related