I need to find closest date in a rails app.

Question

Hi, I need to find the closest date to a given date using a rails named scope.

With MySql I could use DATEDIFF but I want to keep database agnosticism, following doesn't work with Sqlite:

named_scope :closest_to, lambda { |*args|
  date  = args.first
  count = args[1] || 1
  {:limit => count, :order => "ABS(DATEDIFF(hour, date_field, #{date.to_formatted_s :db}))"}
}

Answer 1

One option would be to use something along the lines of:

:limit => count, :select => "*, abs(date('#{date.to_formatted_s :db}') - date('#{date_field}')) as date_diff", :order => "date_diff"

Might be a bit database intensive though, as it has to calculate the date_diff field from all records.

Answer 2

Solved it like this:

named_scope :order_by_closest_to, lambda { |date|
  {:order => %{ABS(strftime('%s', "#{date.to_formatted_s(:db)}") - strftime('%s', value)) asc} }
}

Thus I can mix with other named scopes and limit the number of results so it won't iterate through all records.