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.
How to fetch date ranges that intersect with another range is expertly explained here: Comparing date ranges
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.
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.
