Rails where date is greater than given date query

Question

Trying to search where movies coming out have a release date greater than today's date

 Movie.where('release > ?', Date.today)
ActiveRecord::StatementInvalid: Mysql::ParseError: You have an error in your SQL     syntax;    check the manual that corresponds to your MySQL server version for the right syntax to use near 'release > '2011-09-25')' at line 1: SELECT `movies`.* FROM `movies` WHERE (release > '2011-09-25')

Possible duplicate of [Can you do greater than comparison on a date in a Rails 3 search?](https://stackoverflow.com/questions/4224600/can-you-do-greater-than-comparison-on-a-date-in-a-rails-3-search) — Brad Werth, Dec 05 '17 at 21:54

score 144 · Accepted Answer · edited Jul 03 '19 at 20:58

144

Rails 3+ :

Movie.where('release > ?', DateTime.now)

Pre Rails 3

Movie.where(['release > ?', DateTime.now])

edited Jul 03 '19 at 20:58

Travis

13,311
4
26
40

answered Sep 26 '11 at 01:32

Adam Eberlin

14,005
5
37
49

22

you don't need the array syntax for this anymore. rails 3, 4 :) – oma Jan 29 '14 at 08:29
2

please elaborate @oma – Adam Waite Oct 20 '14 at 13:36
2

@AdamWaite, meaning that the syntax in the Q is actually correct for newer versions – oma Oct 22 '14 at 11:34
Note that if your query param is a `Date` and not a `DateTime`, then you'll likely want to call `.to_time` on the param. This will take into account the common difference between your Rails `Time.zone` (e.g. UTC-5) and your database time zone (should be UTC). So your query would look like: `Movie.where('release > ?', my_date.to_time)`. Aside: You can view your Postgres timezone using: `show timezone;` – stwr667 Feb 24 '21 at 11:48

score 36 · Answer 2 · answered Nov 12 '20 at 15:58

36

In recent versions of rails, you can do this:

User.where(created_at: 3.days.ago..Time.now)

See some other examples here: https://stackoverflow.com/a/24150094

answered Nov 12 '20 at 15:58

Patrick

1,227
14
17

9

This is really nice @Patrick. FWIW if you want an **exclusive** date range, add a 3rd dot in the range, i.e. `User.where(created_at: 3.days.ago...Time.now)` and it works just as you'd expect. – stwr667 Dec 08 '20 at 12:34
6

You can also do `User.where(created_at: ..Time.now)` to leave the lower bound un-bounded. I.e. it would find anything that was created_at a time before Time.now. – Ricky Jun 14 '21 at 17:20

Marian13 · Answer 3 · 2020-12-10T15:55:58.140

17

Update

Rails core team decided to revert this change for a while, in order to discuss it in more detail. See this comment and this PR for more info.

I am leaving my answer only for educational purposes.

new 'syntax' for comparison in Rails 6.1 (Reverted)

Movie.where('release >': DateTime.now)

Here is a link to PR where you can find more examples.

edited Dec 10 '20 at 15:55

answered Jul 21 '20 at 16:45

Marian13

7,740
2
47
51

2

Thanks for keeping this up. Although it got reverted (not merged yet), I still find it a cool and super interesting read. – Daniel Aug 23 '21 at 14:09

score 15 · Answer 4 · answered Jul 06 '21 at 18:15

15

In Ruby 2.7, you can try this:

License.where(expiration: Time.zone.today..)

SELECT "licenses".* FROM "licenses" WHERE "licenses"."expiration" >= '2021-07-06 15:12:05'

answered Jul 06 '21 at 18:15

José Mateus

311
2
5

rinold simon · Answer 5 · 2023-01-17T13:08:42.693

Ruby beginless/endless ranges can also be used as an out-of-the-box solution:

Post.where(id: 1..)

=> Post Load (0.4ms) SELECT "posts".* FROM "posts" WHERE "posts"."id" >= $1 [["id", 1]]

Post.where(id: ..9)

=> Post Load (0.3ms) SELECT "posts".* FROM "posts" WHERE "posts"."id" <= $1 [["id", 9]]

Post.where(id: ...9)

=> Post Load (0.3ms) SELECT "posts".* FROM "posts" WHERE "posts"."id" < $1 [["id", 9]]

Note:

Replace id with your date column release

Replace value with Date.today