This was asked on the forum, so I thought I would place it here too.

Problem

Suppose you have a model that resembles the following:

  range:
    id:                                   
    start_date: {type: date}
    end_date: {type: date}

And you want to know the following: are there any records where either $date1 or $date2 is between start_date and end_date?

Consider that $date2 could be some fixed $offset from $date1, so that the question becomes: are there any records which partially cover the $offset period following $date1?

Using Criterion Objects

Since the BETWEEN construct is unavailable through Propel (at least not as an object or constant), we have to use the knowledge that:

if
  a >= x
and
  a <= y
then
  x <= a <= y

Which gives us:

$c = new Criteria();
 
$date1 = '2007-08-20';
$date2 = '2008-08-20';
 
// test against date1
$date1Criterion = $c->getNewCriterion(RangePeer::START_DATE, $date1, Criteria::LESS_EQUAL);
 
// test against date2
$date2Criterion = $c->getNewCriterion(RangePeer::START_DATE, $date2, Criteria::LESS_EQUAL);
 
// conjunction
$date1Criterion->addAnd($c->getNewCriterion(RangePeer::END_DATE, $date1, Criteria::GREATER_EQUAL));
$date2Criterion->addAnd($c->getNewCriterion(RangePeer::END_DATE, $date2, Criteria::GREATER_EQUAL));
 
// disjunction
$date1Criterion->addOr($date2Criterion);
$c->add($date1Criterion);

Using Criteria::CUSTOM

Or if you absolutely must have your BETWEEN, try this:

$c = new Criteria();
 
$date1 = '2007-08-20';
$date2 = '2008-08-20';
 
$c->add(RangePeer::START_DATE, "'{$date1}' between ".RangePeer::START_DATE." and ".RangePeer::END_DATE, Criteria::CUSTOM);
$c->addOr(RangePeer::START_DATE, "'{$date2}' between ".RangePeer::START_DATE." and ".RangePeer::END_DATE, Criteria::CUSTOM);

Note that the first arguments to Criteria::add() and Criteria::addOr() can be any column, but they must be the same column.

Note also that when providing the argument, {$dateN} appears between single quotes. It must appear quoted (and escaped) in order for this query to return correct (or any) results.

The problem is simple. I have a booking system for appartments (or whatever else). Reservations are stored in a Reservation table, and have a begin_date and an end_date column.

I want to find the existing reservations for an appartment between two dates.

The data I have is $appartment_id, $begin_date and $end_date. I want reservations starting or ending between the two dates, or starting before the $begin_date and ending after the $end_date. That's how it would be translated into a SQL WHERE:

reservation.APPARTMENT_ID = $appartment_id 
AND 
(((reservation.START_DATE > $begin_date AND reservation.START_DATE < $end_date) 
  OR 
  (reservation.END_DATE > $begin_date AND reservation.END_DATE < $end_date)) 
  OR 
  (reservation.END_DATE > $end_date AND reservation.START_DATE < $begin_date))

Of course, I'd prefer to use Propel for that. Is it tricky? Not that much.

class Appartment extends BaseAppartment {
 
  public function findReservations($begin_date, $end_date)
  {
    $c = new Criteria();
    $c->add(ReservationPeer::APPARTMENT_ID, $this->getId());
 
    // Find reservations beginning between the search period
    $criterion1 = $c->getNewCriterion(
      ReservationPeer::START_DATE, $begin_date, Criteria::GREATER_THAN
    )->addAnd($c->getNewCriterion(
      ReservationPeer::START_DATE, $end_date, Criteria::LESS_THAN
    ));
 
    // Find reservations ending between the search period
    $criterion2 = $c->getNewCriterion(
      ReservationPeer::END_DATE, $begin_date, Criteria::GREATER_THAN
    )->addAnd($c->getNewCriterion(
      ReservationPeer::END_DATE, $end_date, Criteria::LESS_THAN
    ));
 
    // Find reservations beginning before the search period and ending after
    $criterion3 = $c->getNewCriterion(
      ReservationPeer::END_DATE, $end_date, Criteria::GREATER_THAN
    )->addAnd($c->getNewCriterion(
      ReservationPeer::START_DATE, $begin_date, Criteria::LESS_THAN
    ));
 
    // Combine all that with a OR
    $c->add($criterion1->addOr($criterion2)->addOr($criterion3));
 
    return = ReservationPeer::doSelect($c);
  }
}

Say you look for the objects of class Foo being created between $from_date and $to_date. This should do the trick:

$c = new Criteria();
$criterion = $c->getNewCriterion(FooPeer::CREATED_AT , date('Y-m-d', $from_date), Criteria::GREATER_EQUAL  );
$criterion->addAnd($c->getNewCriterion(FooPeer::CREATED_AT , date('Y-m-d', $to_date), Criteria::LESS_EQUAL ));
$c->add($criterion);
$shows = FooPeer::doSelect($c);