Finding records between date range

Question

I'm trying to find records whose start and end date range over a particular date. Date is random and :start_date and :end_date are attributes of the prices entity.

date = Time.now
record_i_want = Price.where(date => :start_date .. :end_date)

Thank you.

Answer 1

You can simply do

Price.where(:date => start_date..end_date)

This will result in the following SQL( for start and end dates - '2014-03-27', '2014-03-28')

SELECT `prices`.* FROM `prices` WHERE (`prices`.`date` BETWEEN '2014-03-27' AND '2014-03-28')

EDIT: Realized that this is the query you are looking for. Thanks, Coenwulf for pointing it out

Price.where(['start_date < ? AND end_date > ?', date, date])

Answer 2

You want to select rows where your date is greater than the start_date and less than the end_date. You can specify the appropriate SQL where clause parameterized in your call to where like so:

Price.where([":date >= start_date AND :date <= end_date", {date: Date.today})

That will give you all the prices that match. If you know you'll get only one you can get it by calling first.

Price.where([":date >= start_date AND :date <= end_date", {date: Date.today}).first

Make any appropriate adjustment to the >= and <= if you want to exclude the start_date and/or the end_date from the results. If for example the Price is valid starting on the start_date but isn't valid through the end_date you can change the clause to:

":date >= start_date AND :date < end_date"

Answer 3

This should work:

def get_record_by_date(date)
  Price.where([start_date.to_i < date.to_i] && [end_date.to_i > date.to_i])
end