Ransack: Problem with _lteq on a Date field

[timoschilling]

I have a Model with a Date field, and I want to query all Records lower or equal to a Date:

Appointment.search(date_lteq: "2014-10-28").result
# => "SELECT `appointments`.* FROM `appointments`  WHERE (`appointments`.`date` <= '2014-10-27 23:00:00')"

This results in a query that not covers the Okt. 28.

This behavior seams right if I pass a Time in, but if I pass a Date, I will search for the whole day.

Ransack should work internally like this:

Appointment.search(date_lteq: "2014-10-28".to_time.utc.end_of_day).result.to_sql
# => "SELECT `appointments`.* FROM `appointments`  WHERE (`appointments`.`date` <= '2014-10-27 23:59:59')"

Source of this issue is activeadmin/activeadmin#2374 from @baxang

[jonatack]

Why not use a ransacker (see the wiki for examples and more details) to convert the query from datetimes to dates, using something like:

ransacker :date_only_query { Arel.sql('date(datetime_field)') }

Please don't use a hack like the one in that ActiveAdmin thread when a one-line solution will do.

I've also seen issues like this arise from specifying a time zone in Rails, but not in the PAAS. For example, with Heroku: config:set TZ=Europe/Berlin

[timoschilling]

Do you mean I should define a Ransacker for the date search?

class Appointment < ActiveRecord::Base
  ransacker ... do
    # ...
  end
end

[timoschilling]

I thing thats not a change, for me it looks like a bug.

Appointment.search(date_lteq: "2014-10-28").result

this queries:

SELECT `appointments`.* FROM `appointments`  WHERE (`appointments`.`date` <= '2014-10-28 00:00:00')

but in my opinion it should query:

SELECT `appointments`.* FROM `appointments`  WHERE (`appointments`.`date` <= '2014-10-28 23:59:59')

otherwise its not cover the '2014-10-28' and is the same (with a 1 sec difference) as:

Appointment.search(date_lt: "2014-10-28").result

SELECT `appointments`.* FROM `appointments`  WHERE (`appointments`.`date` < '2014-10-28 00:00:00')

(For the current discussion I skip the timezone problem)

[jonatack]

Ransack is using datetimes and doing the same thing as Arel/ActiveRecord with datetimes.

In the rails console, either of these two searches:

Event.search(date_lteq: '2014-10-28').result.to_sql
Event.search(date_lteq: Time.new(2014, 10, 28)).result.to_sql

generates the same SQL as

Event.where('events.date <= ?', Time.new(2014, 10, 28)).to_sql 

 => "SELECT \"events\".* FROM \"events\" WHERE (events.date <= '2014-10-28 00:00:00.000000')" 

If you want Ransack to do the equivalent of this in ActiveRecord:

Event.where('events.date <= ?', Time.new(2014, 10, 28, 23, 59, 59)).to_sql 

 => "SELECT \"events\".* FROM \"events\" WHERE (events.date <= '2014-10-28 23:59:59.000000')" 

then do the same with the Ransack search query (these two queries are equivalent):

Event.search(date_lteq: '2014-10-28 23:59:59').result.to_sql
Event.search(date_lteq: Time.new(2014, 10, 28, 23, 59, 59)).result.to_sql

which you can do using a custom predicate and ransacker.

Or, just convert from datetimes to dates using the one-line ransacker in my previous comment (see the Ransack wiki for more info). That is what I would do.

[aldrinmartoq]

I was looking into this problem, my aproximation was create the following predicates:

# config/initializers/ransack.rb
Ransack.configure do |config|
  config.add_predicate 'dategteq',
    arel_predicate: 'gteq',
    formatter: proc { |v| v.beginning_of_day },
    type: :date

  config.add_predicate 'datelteq',
    arel_predicate: 'lteq',
    formatter: proc { |v| v.end_of_day },
    type: :date
end

Restart the server. Now you can do:

Model.search(created_at_dategteq: '2017-07-31').result.to_sql
 => "SELECT \"models\".* FROM \"models\" WHERE (\"models\".\"created_at\" >= '2017-07-31 04:00:00')"
Model.search(created_at_datelteq: '2017-07-31').result.to_sql
 => "SELECT \"models\".* FROM \"models\" WHERE (\"models\".\"created_at\" <= '2017-08-01 03:59:59.999999')"

So far it works well, hope this helps.