How to Compare and fetch date in Cakephp ?

Question

I am trying to make an availability calendar and need to know how can I compare dates when fetching it.

My DB is

id
start_date
end_date
status

Now suppose I want to fetch booking in next the month, i.e. from 2010-03-01 to 2010-04-01. How should I fetch this data? I did try comparing directly using an and condition but it didn't help.

The format in the DB is yyyy-mm-dd and I used the same to compare. But direct comparison does not work.

Answer 1

Assuming your table is named Events and your dates are stored in $start_date and $end_date, I believe this should work:

$results = $this->Event->find('all', array(
    'conditions' => array(
        'start_date >=' => $start_date,
        'end_date <'    => $end_date
    )
));

It will get all records that start and end within your range.

Answer 2

How to fetch date ranges that intersect with another range is expertly explained here: http://stackoverflow.com/questions/143552/comparing-date-ranges/143568#143568

In summary, you probably want to query for something like this:

$start = date('Y-m-d');
$end = date('Y-m-d', strtotime('+1 month'));

$conditions = array('Event.start <=' => $end, 'Event.end >=' => $start);

$this->Event->find('all', array('conditions' => $conditions));

This will find all events within the next month. They may start earlier or end later, but they'll intersect with the month.