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.

Autocratic

One of my most loathed aspects of Active Record is no obvious and intuitive support for gt/lt-or-equals (or ranges) in where() clauses! Passing in strings like "column >= ?" just makes me wonder why I dont just type the query out myself in the first place. – Sports

My answer on this question gives more details for the latest versions of Ruby and Rails as of this writing (3.0.2 and 6.1 respectively). – Autocratic

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)

Autocratic

The verbosity is not necessary and could be done on one line. I'm just using variables to make it explicitly clear what each step returns. – Autocratic

Using the syntax before User.where(last_deposit: ..10.days.ago) Give me the exception syntax error, unexpected ')' – Washable

@Washable what version of Ruby are you on? ruby --version – Autocratic

I'm on ruby 2.6.5p114 (2019-10-01 revision 67812) – Washable

Endless ranges are in Ruby 2.6+ and beginless ranges were introduced in Ruby 2.7 so nothing is wrong with your system. – Autocratic

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')

Panchromatic

That works decently for User.where('users.last_deposit` < ?', 10.days.ago)` but not for the >= case I wanted. Switching it to 10.days.ago...Time.at(Float::INFINITY) also doesn't work. It generates SQL but an invalid date (at least for PG). – Autocratic

Three dots between the range-limits gets you a >= on the 'from' value with 'AND <' on the 'to' value . Two dots gets you a 'BETWEEN' sql query using 'from' and 'to'. (in Rails 4.0.13) – Krystinakrystle

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?

Lonna

Interesting. I did not know about Datetime::Infinity. It's odd that it must be instantiated as Float::INFINITY does not. I realize that one is a constant for a Float value and the other is a class but it would be easier to remember if it were symmetric. – Autocratic

I can confirm that it does not work with Rails 4 though. User.where(created_at: 1.day.ago..DateTime::Infinity.new) results in a MySQL error as it has #<Date::Infinity:0x0...> as the ending bound instead of converting to use > or something else. – Autocratic

Odd, could be a MySQL thing. Oh well, cases like this I just use the far future and/or far past when infinity isn't working well enough. User.where(created_at: 1000.years.ago..1000.years.from_now). Though that's not much better than the Time.at() solutions. Tried converting to to dateTIme, but got similarly odd errors. Might also find it interesting to note that User.where(created_at: 1.day.ago.to_date..Float::INFINITY) works. – Lonna

Are you on Rails 5 by chance? My gut says the fact that as the query goes from Ruby objects to an SQL string it's not being converted properly to something a database would understand hence the Ruby object's inspect output of #<Date::Infinity:0x0...object_address> format. This could've been updated in Rails 5 which is why it works in some cases. – Autocratic

Quite possible, I have apps in Rails 4 and 5. Though unsure if that's the issue. Something to look into. – Lonna

Could you provide any valid range with DateTime::Infinity.new(-1)? All my attempts failed. – Abrahamsen

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

Huxham

@Abrahamsen irb> (Float::INFINITY..Time.current.to_datetime).include? 2.days.ago.to_datetime => false irb> (-Float::INFINITY..Time.current.to_datetime).include? 2.days.ago.to_datetime => true This does not work with DateTime::Infinity.new Only one range I did with DateTime Infinity is useless: irb> (-DateTime::Infinity.new..DateTime::Infinity.new).include? 2.days.ago.to_datetime => false irb> (DateTime::Infinity.new(-1)..DateTime::Infinity.new).include? 2.days.ago.to_datetime => false – Huxham

While your examples in you comment are correct usage in a query actually has nothing to do with inclusion in the range. Instead it has to do with how Arel used to handle between logic Source. You can see it directly relied on Float::INFINITY.... – Croom

Newer iterations of Arel (now part of rails) changed this to be more flexible by relying on messages like infinite? and boundable? Source which makes a lot more sense and it means you can create your own objects to pass in if you so desire. – Croom

Answer 5

Try this:

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

Stolon

Wouldn't last_deposit have to be an ARel column reference for this to work? – Autocratic

last_deposit = User.arel_table[:last_deposit] I believe. – Autocratic

As it stands, this answer is incomplete / incorrect - See Aaron's comments. – Flophouse