Rails `where` for time less than queries

Question

Setup

Rails' where method can take a range in a hash to generate a query that will search for a value that is within the range. For example:

User.where(cash_money: 10..1000)
#=> SELECT `users`.* FROM `users`  WHERE (`users`.`cash_money` BETWEEN 10 AND 1000)

This can also be used with timestamps like

User.where(last_deposit: 10.days.ago..1000.days.ago)
#=> SELECT `users`.* FROM `users`  WHERE (`users`.`last_deposit` BETWEEN '2014-05-19 14:42:36' AND '2011-09-02 14:42:36')

I've found that you can do a simple less than or greater than with numbers using the hash syntax like this

User.where(cash_money: 10..Float::INFINITY)
#=> SELECT `users`.* FROM `users`  WHERE (`users`.`cash_money` >= 10)

and the same can be done with -Float::INFINITY for less than queries.

Question

Is there a way to do this with timestamps so I can get a query like the following?

SELECT `users`.* FROM `users`  WHERE (`users`.`last_deposit` >= '2014-05-19 14:42:36')

I cannot use Float::INFINITY or Date::Infinity with a range as they both error with ArgumentError: bad value for range.

Current Simple Solution

User.where('`users`.`last_deposit` >= ?', 10.days.ago)

will generate the same SQL but if this can be done with objects other than strings, I'd like to do so.

Potential (Meh) Answer

This is kind of lousy but it could be done with ranges using Time.at(0) and Time.at(Float::MAX). I have a feeling these could result in equally lousy SQL queries though.

Answer 1

Edit 2 5/9/20

If you are using Ruby 2.6 you can do this with endless ranges and in Ruby 2.7 you can use begin-less ranges.

E.g.:

# Ruby >= 2.6
User.where(last_deposit: 10.days.ago..)

generates

SELECT "users".* FROM "users" WHERE "user"."last_deposit" >= '2020-04-29 21:58:39.109419'"

and

# Ruby >= 2.7
User.where(last_deposit: ..10.days.ago)

generates

SELECT "users".* FROM "users" WHERE "users"."last_deposit" <= '2020-04-29 22:01:05.582055'

Edit

This is now possible in Rails 5!

User.where(last_deposit: 10.days.ago..DateTime::Infinity.new)

will generate the SQL

SELECT `users`.* FROM `users` WHERE (`users`.`last_deposit` >= '2018-06-30 17:08:54.130085').

Original (and Rails < 5) Answer

It does not appear as if there is a way to use basic where hash syntax to generate a greater than or less than query for timestamps. The simplest and most readable way is outlined in my question under Current Simple Solution.

Another way to do it makes use of ARel but you have to make some less commonly seen calls. First you can get a handle to the AR class' ARel table, access the column, pass the result of the greater than gt, greater than or equal to gteq, less than lt, and/or less than or equal to lteq method with an argument to where.

In the situation above this would be done like:

last_deposit_column = User.arel_table[:last_deposit]
last_deposit_over_ten_days_ago = last_deposit_column.gteq(10.days.ago)
User.where(last_deposit_over_ten_days_ago)

Answer 2

Did you try this?:

User.where(last_deposit: Time.at(0)...10.days.ago)

SQL:

SELECT `users`.* FROM `users`  WHERE (`users`.`last_deposit` >= '1970-01-01 00:00:00' AND `users`.`last_deposit` < '2015-01-10 17:15:19')

Answer 3

You need to use the appropriate infinity. A timestamp is a DateTime not a Date. Use DateTime::Infinity.new instead or DateTime::Infinity.new(-1) for negative infinite.

Related: Is there a way to express 'Infinite Time' in Ruby?

Answer 4

Rails 5 (and ruby < 2.7)

User.where(last_deposit: 10.days.ago.to_datetime..DateTime::Infinity.new)

that should also work: User.where(last_deposit: 10.days.ago..DateTime::Infinity.new)

but problematic scope for condition before some date:

User.where(last_deposit: DateTime::Infinity.new..10.days.ago.to_datetime)

DateTime::Infinity.new right now is equal to Float::INFINITY. Time.current (so 3.minutes.ago also) does not support range with Float.

Converting it to datetime solves the issue

Answer 5

Try this:

User.where(last_deposit.gt(10.days.ago))