How do I do this? Model.where("created_at >= #{Time.now - 5.days}")

Question

This has been bugging me for a while...

How can I string interpolate a datetime in Rails ActiveRecord queries?

# Works, but supeh ugleh:
Model.where("created_at >= ?", Time.now - 5.days)

# How do I do this?
Model.where("created_at >= #{Time.now - 5.days}")
# As is, it produces the following error message:
# ActiveRecord::StatementInvalid: PG::Error: ERROR:  syntax error at or near ...

The reason I care is for code readability:

# I like this better:
Model.where("created_at >= #{Time.now - 5.days} OR" + \
            "updated_at >= #{Time.now - 3.days}")

# than this:
Model.where("created_at >= ? OR updated_at >= ?", Time.now - 5.days, Time.now - 3.days)

Gyatt

Answer 1

I'd advise against using string interpolation for this, there are many sharp edges and you'll probably have more fun bobbing for apples in a bucket of fish hooks. You should do it this way:

Model.where(
    'created_at >= :five_days_ago or updated_at >= :three_days_ago',
    :five_days_ago  => Time.now - 5.days,
    :three_days_ago => Time.now - 3.days
)

Using (well) named placeholders gives you the readability and position independence that you think string interpolation offers but nicely sidesteps the quoting, timezone, and format issues that string interpolation forces on you.

But how do you safely use string interpolation? There are a few things you must handle yourself:

1. Quoting and escaping.
2. Timestamp formats.
3. Maybe timezones too.

ActiveRecord will take care of all this nonsense for you.

Don't try to do the quoting yourself, use the driver's quoting methods. You will have access to connection.quote for properly quoting strings.

Any database will know what to do with ISO 8601 timestamps and there is a convenient iso8601 method for that. ISO 8601 also conveniently includes the timezone and the database should be able to parse that (but if it can't then you'll have to convert your times to UTC by hand with .utc).

So, to be safe:

Model.where("created_at >= #{connection.quote((Time.now - 5.days).utc.iso8601)} " + \
         "OR updated_at >= #{connection.quote((Time.now - 3.days).utc.iso8601)}")

Not so pretty now is it? With ISO 8601 timestamps you should be safe replacing the connection.quote calls with simple single quotes:

Model.where("created_at >= '#{(Time.now - 5.days).utc.iso8601}' " + \
         "OR updated_at >= '#{(Time.now - 3.days).utc.iso8601}'")

but you still have a lot of noise and ugliness and you'll be developing bad habits.

We're not partying like PHP programmers in 1999 so don't give in to false laziness by using string interpolation in your SQL, use named placeholders.

Puzzle

And that, ladies and gentlemen, is why mu has 74.2k rep at the time of this writing. I can only hit the upvote once, but I really appreciate the clarity and thoroughness of your answer. – Gyatt

"bobbing for apples in a bucket of fish hooks" epic – Attaway

Answer 2

Old question, but my favoured method is:

Model.where(created_at: 5.days.ago..Time.current)

Much prettier and more readable.

Also, Rails 3.2 introduced some Active Support helper methods to get some common ranges, Time#all_day, Time#all_week, Time#all_quarter and Time#all_year, so you could for instance do:

Model.where(created_at: Time.current.all_week)

Palaver

Answer 3

You can use endless (>= Ruby 2.6) and beginless (>= Ruby 2.6) ranges:

Endless: Suffix it with the range operator .. if you want after some date:

Model.where(created_at: (Time.zone.now - 5.days)..)

Beginless: Prefix it with the range operator .. if you want before some date:

Model.where(created_at: ..(Time.zone.now - 5.days))

Effluent