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);

In an i18n application, if your template uses the input_date_tag() helper, the format of the date sent to the submit action will depend on the user culture. But then, how can you handle this date to, say, store it in a database in a culture independent format?

The answer lies in the sfI18N class:

$date= sfContext::getInstance()->getRequest()->getParameter('birth_date');
$user_culture = sfContext::getInstance()->getUser()->getCulture();
list($d, $m, $y) = sfI18N::getDateForCulture(
  $date,
  $user_culture
);

Now you have the day, month and year of the date in the $d, $m and $y variables, and you can do whatever you want with them.

If you use a Propel date setter, you can even call it directly with:

$person->setBirthDate("$y-$m-$d");

The same applies for the sfI18N::getTimestampForCulture() method.

Sometimes you need a list of the dates between two dates.

    /**
     * Returns an array with the dates between to dates given.
     *
     * @link http://us3.php.net/manual/en/function.date.php#AEN25217
     * 
     * @param mixed $startdate Timestamp or strtotime() recognizeable string
     * @param mixed $enddate Timestamp or strtotime() recognizeable string
     * @param string[optional] $format date() format string
     * @return mixed Array of timestamps or dates if given format
     */
    public static function dates_between($startdate, $enddate, $format=null){
 
        (is_int($startdate)) ? 1 : $startdate = strtotime($startdate);
        (is_int($enddate)) ? 1 : $enddate = strtotime($enddate);
 
        if($startdate > $enddate){
            return false; //The end date is before start date
        }
 
        while($startdate < $enddate){
            $arr[] = ($format) ? date($format, $startdate) : $startdate;
            $startdate += 86400;
        }
        $arr[] = ($format) ? date($format, $enddate) : $enddate;
 
        return $arr;
    }

You can give it a timestamp or a string date. It will use date() to format the output if given a $format.

select_date_tag is not available in 'rich' version. Waiting for a real enhancement, this is my workaround.

This is the RichDateHelper.php file:

<?php
 
use_helper('Form');
 
function rich_select_date_tag($name, $value = null, $options = array(), $html_options = array()) {
 
    $context = sfContext::getInstance();
  if (isset($options['culture']))
  {
    $culture = $options['culture'];
    unset($options['culture']);
  }
  else
  {
    $culture = $context->getUser()->getCulture();
  }
 
    // register our javascripts and stylesheets
  $langFile = '/sf/js/calendar/lang/calendar-'.strtolower(substr($culture, 0, 2));
  $jss = array(
    '/sf/js/calendar/calendar',
    is_readable(sfConfig::get('sf_symfony_data_dir').'/web/'.$langFile.'.js') ? $langFile : '/sf/js/calendar/lang/calendar-en',
    '/sf/js/calendar/calendar-setup',
  );
  foreach ($jss as $js)
  {
    $context->getResponse()->addJavascript($js);
  }
 
  $js = '
        function updateSelect(cal) {
            var date = cal.date;
            var selectMonth = document.getElementById("'.get_id_from_name($name).'_month");
            selectMonth.selectedIndex = date.getMonth();
            var selectDay = document.getElementById("'.get_id_from_name($name).'_day");
            selectDay.selectedIndex = (date.getDate() - 1);
            var selectYear = document.getElementById("'.get_id_from_name($name).'_year");
            selectYear.selectedIndex = (date.getFullYear() - '.$options['year_start'].');
        }
    document.getElementById("trigger_'.$name.'").disabled = false;
    Calendar.setup({
            inputField : "'.$name.'_rich_sel_date",
            ifFormat : "%Y-%m-%d",
            button : "trigger_'.$name.'",
            singleClick : true,
            onUpdate : updateSelect,
            showsTime : false,
            range : ['.$options['year_start'].', '.$options['year_end'].'],
            showOthers : false,
            cache : 1,
            weekNumbers : false,
            firstDay : 1
    });
  ';
 
    $html = select_date_tag($name, $value, $options, $html_options);
 
  // calendar button
  $calendar_button = '...';
  $calendar_button_type = 'txt';
  if (isset($options['calendar_button_img']))
  {
    $calendar_button = $options['calendar_button_img'];
    $calendar_button_type = 'img';
    unset($options['calendar_button_img']);
  }
  else if (isset($options['calendar_button_txt']))
  {
    $calendar_button = $options['calendar_button_txt'];
    $calendar_button_type = 'txt';
    unset($options['calendar_button_txt']);
  }
 
  if ($calendar_button_type == 'img')
  {
    $html .= image_tag($calendar_button, array('id' => 'trigger_'.$name, 'style' => 'cursor: pointer'));
  }
  else
  {
    $html .= content_tag('button', $calendar_button, array('type' => 'button', 'disabled' => 'disabled', 'onclick' => 'return false', 'id' => 'trigger_'.$name));
  }
 
  // add javascript
  $html .= content_tag('script', $js, array('type' => 'text/javascript'));
  $html .= '<div id="'.$name.'_rich_sel_date" style="display: inline;"></div>';
 
  return $html;
}