mysql BETWEEN等效于MongoDB

Question

In PHP & MongoDB, i am trying to read all instances of events for a specific calendar and a specific period. Each event has start and end date. They are of MongoDate type in mongodb.

I use below condition to get the events from mongo.

$condition = array(
 'start_time'    => array('$gte'    =>  new MongoDate($start_day)), 
 'end_time'      => array('$lte'    => new MongoDate($end_day))
);

Here is my problem, i am missing the events whose start date is to my date range.

For example event starts on 01-july-2014
event ends on 10-july-2014

my mongo query - give me events from 5-july to 12-july

$condition = array(
  'start_time'    =>    array('$gte'    => '5-july-2014'), 
  'end_time'      =>    array('$lte'    => '12-july-2014')
);

When i run above mentioned event is not returned.

Any suggestions are welcome?

In mysql BETWEEN helps me to get the required results.

Answer 1

Your query selects any events which are completely contained in the given date range, but when I understood you correctly you want events which partly or completely overlap the date-range.

A date-range overlaps another date-range when the start of A is before the end of B and the end of A is after the start of B. So try

'event_start'    => array('$lte'    =>  new MongoDate($search_end)), 
'event_end'      => array('$gte'    => new MongoDate($search_start))

Another pitfall I should mention is that a MongoDate doesn't represent a single day, but an exact point in time. When you omit the time, you get midnight at that date. So '$lte' => '12-july-2014' gives you 2014-04-12 0:00:00.000 which will not select anything on july 12th. When you want to include that day, either manually set a time like 12-july-2014 23:59:59 or just use the next day.