I have two datetime columns in a User/users table: created_at and birthdate. I'd like to find users whose birthdate is less than 13 years before their creation date.
The equivalent Rails if statement would be ((created_at - birthdate) < 13.years)
How do I translate this to an ActiveRecord query (one that'll work on PostgreSQL)? Is this even possible, or will I have to iterate over all records manually?
The easiest way to do this is to use an interval, then it is pretty much a straight transliteration of the Rails version:
User.where(%q{created_at - birthdate < interval '13 years'})
The difference between two timestamps (or a timestamp and a date) is an interval so you just need the appropriate value on the right side of your comparison.
interval doesn't work on SQLite. I had to log into my production server using PostgreSQL to test it out.
You simply have to formulate that in PostgreSQL syntax inside your where clause.
For MySQL this would look similar to this using the datediff function:
User.where("DATEDIFF(created_at, birthdate) > (13 * 365)")
13*356 is there to represent 3 years in days since datediff returns difference in days.
I would then encapsulate that in a scope-like function like the following:
class User < ActiveRecord::Model
def self.age_difference(years)
where("DATEDIFF(created_at, birthdate) > (? * 365)", years)
end
end
So you can call it:
User.age_difference(13).each do |user|
puts user.inspect
end
I guess it's similar in Postgres.
