Selecting table entries where a given date is between the :start date and :end date

Question

I have an object that has a start date and an end date, in order to represent the time that the object is valid. Given a date, is there a way to only select those objects that have valid ranges that contain the date?

I tried fiddling with between, but couldn't get the syntax right.

Thanks!

Answer 1

If you were doing this for "given_date".

select *
from table
where start_date <= given_date
and end_date >= given_date

Answer 2

This is how you'd do it using active record.

Foo.find(:all, :conditions => ['valid_from <= ? and valid_to >= ?', valid_date, valid_date])

Answer 3

This is often implemented using a named scope that does the appropriate restriction that identifies which records are visible at the current point in time:

class MyRecord < ActiveRecord::Base
  named_scope :visible,
    :conditions => 'visible_from<=UTC_TIMESTAMP() AND visible_to>=UTC_TIMESTAMP'
end

This can be altered to use place-holders for more arbitrary dates:

class MyRecord < ActiveRecord::Base
  named_scope :visible_at, lambda { |date| {
    :conditions => [
      'visible_from<=? AND visible_to>=?',
      date, date
    ]
  }}
end

Presumably your dates are stored as UTC, as it is a considerable nuisance to convert from one local-time to another for the purposes of display.

You can select all visible models like this:

@records = MyRecord.visible.all
@records = MyRecord.visible_at(2.weeks.from_now)