DateTime arithmetic in an ActiveRecord query (PostgreSQL)

Question

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?

Answer 1

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.