How to compare 2 date ranges in DB collection

Wed Jun 11, 2014 4:41 pm

Thanks for the VERY detailed answer
I found 9 use cases for this query, I test the other 4 and here is the total result:
5 use cases - Passed OK.
4 use cases - Failed

Here are the details for the last 3 use cases:

4 test:
2014-06-29 17:31:00.000 - 2014-06-29 18:32:00.000 - overlap with 2nd and 3rd items. The start date&time is where NO event occur but the end date&time is in existing event

{
"$or": [
{"$and": [ {"EventStartDate": {"$gt": "2014-06-29 17:31:00.000"} }, {"EventEndDate": {"$gt": "2014-06-29 18:32:00.000"} } ] },
{"$and": [ {"EventStartDate": {"$lt": "2014-06-29 17:31:00.000"} }, {"EventEndDate": {"$lt": "2014-06-29 18:32:00.000"} } ] }
]
}

Return 3 items (means no event exists at this time) - Failed.

5 test:
2014-06-29 13:31:00.000 - 2014-06-29 15:32:00.000 - overlap with 1st items. The start date&time is where event occur but the end date&time is where NO event occur
{
"$or": [
{"$and": [ {"EventStartDate": {"$gt": "2014-06-29 13:31:00.000"} }, {"EventEndDate": {"$gt": "2014-06-29 15:32:00.000"} } ] },
{"$and": [ {"EventStartDate": {"$lt": "2014-06-29 13:31:00.000"} }, {"EventEndDate": {"$lt": "2014-06-29 15:32:00.000"} } ] }
]
}

Return 3 items (means no event exists at this time) - Failed.

6 test:
2014-06-29 13:31:00.000 - 2014-06-29 18:32:00.000 - overlap with 1st, 2nd and 3rd items. The start date&time is where 1st event occur and the end date&time is where 2nd and 3rd events occur
{
"$or": [
{"$and": [ {"EventStartDate": {"$gt": "2014-06-29 13:31:00.000"} }, {"EventEndDate": {"$gt": "2014-06-29 22:32:00.000"} } ] },
{"$and": [ {"EventStartDate": {"$lt": "2014-06-29 13:31:00.000"} }, {"EventEndDate": {"$lt": "2014-06-29 22:32:00.000"} } ] }
]
}

Return 1 items while its overlap all 3 events - Failed.

7 test:
2014-06-29 13:31:00.000 - 2014-06-29 22:32:00.000 - overlap with 1st, 2nd and 3rd items. The start date&time is where 1st event occur and the end date&time is AFTER 2nd and 3rd events occur
{
"$or": [
{"$and": [ {"EventStartDate": {"$gt": "2014-06-29 13:31:00.000"} }, {"EventEndDate": {"$gt": "2014-06-29 18:32:00.000"} } ] },
{"$and": [ {"EventStartDate": {"$lt": "2014-06-29 13:31:00.000"} }, {"EventEndDate": {"$lt": "2014-06-29 18:32:00.000"} } ] } ] }

Return 3 items (means no event exists at this time) - Failed.

8 test:
2014-06-29 09:31:00.000 - 2014-06-29 22:32:00.000 - overlap with 1st, 2nd and 3rd items. The start date&time is BEFORE 1st event occur and the end date&time is AFTER 2nd and 3rd events occur (means - all DB entries)
{
"$or": [
{"$and": [ {"EventStartDate": {"$gt": "2014-06-29 09:31:00.000"} }, {"EventEndDate": {"$gt": "2014-06-29 18:32:00.000"} } ] },
{"$and": [ {"EventStartDate": {"$lt": "2014-06-29 09:31:00.000"} }, {"EventEndDate": {"$lt": "2014-06-29 18:32:00.000"} } ] } ] }

Return 0 items (means no event exists at this time) - Passed OK.

9 test:
2014-06-29 09:31:00.000 - 2014-06-29 15:32:00.000 - overlap with 1st item (the start date&time occur before event and the end date&time occur after the event - surrounding the event)
{
"$or": [
{"$and": [ {"EventStartDate": {"$gt": "2014-06-29 09:31:00.000"} }, {"EventEndDate": {"$gt": "2014-06-29 15:32:00.000"} } ] },
{"$and": [ {"EventStartDate": {"$lt": "2014-06-29 09:31:00.000"} }, {"EventEndDate": {"$lt": "2014-06-29 15:32:00.000"} } ] }
]
}

Return 2 items - Passed OK.

So in total: 5 Passed - OK, 4 Failed,
Can you please check?

In addition I notice that in case I will replace all places that I have "and" with "or" and all place with "or" to "and" it will return the event/s that overlaped or blank in case this time is available, is it correct?

i.e.
{
"$and": [
{"$or": [ {"EventStartDate": {"$gt": "2014-06-29 14:21:00.000"} }, {"EventEndDate": {"$gt": "2014-06-29 14:22:00.000"} } ] },
{"$or": [ {"EventStartDate": {"$lt": "2014-06-29 14:21:00.000"} }, {"EventEndDate": {"$lt": "2014-06-29 14:22:00.000"} } ] }
]
}

p.s. while doing it we still face the same error as for test 4 and 6 (means no items return back)

Thu Jun 12, 2014 10:47 pm

Hi Yaniv.

Our solution for you is to find items which is NOT OVERLAPED by items.

You provide us use cases which is OVERLAPED by items.

So please read my previous message again.

Thanks and regards.

Thu Jun 12, 2014 11:29 pm

I read it again,
I have no issue to search events that are NOT OVERLAPPED the user input dates,
But while searching for each user date range what are the items that are NOT OVERLAPPED I'm getting wrong response in those cases:

4 test:
2014-06-29 17:31:00.000 - 2014-06-29 18:32:00.000
This not overlapped 1 event, so the result should be 1 item (1st item) but it returns all 3 items.

5 test:
2014-06-29 13:31:00.000 - 2014-06-29 15:32:00.000
This not overlapped 2 event, so the result should be 2 item (2nd and 3rd) but it returns all 3 items.

6 test:
2014-06-29 13:31:00.000 - 2014-06-29 18:32:00.000
This not overlapped 0 event, so the result should be 0 item but it returns 1 items.

7 test:
2014-06-29 13:31:00.000 - 2014-06-29 22:32:00.000
This not overlapped 0 event, so the result should be 0 item but it returns 3 items.

How can I identify the items that are NOT OVERLAPPED in those cases? the result contains wrong number of events that are NOT OVERLAPPED.

Fri Jun 13, 2014 2:05 am

Yaniv

Please specify what issue you can not solve with "not overlaped count" answer?

Thank you for your detailed answer about NOT overlaped WRONG use cases.

I've modified "where" code and here is worked one:

precode

4 test:
2014-06-29 17:31:00.000 - 2014-06-29 18:32:00.000

{
"$or": [
{"$and": [
{"EventStartDate": {"$gt": "2014-06-29 17:31:00.000"} },
{"EventEndDate": {"$gt": "2014-06-29 17:31:00.000"} },

Code: Select all

         {"EventStartDate": {"$gt": "2014-06-29 18:32:00&#46;000"} }, 
         {"EventEndDate": {"$gt": "2014-06-29 18:32:00&#46;000"} } 
     ] 
 },  

 {"$and": [  
         {"EventStartDate": {"$lt": "2014-06-29 17:31:00&#46;000"} }, 
         {"EventEndDate": {"$lt": "2014-06-29 17:31:00&#46;000"} }, 

         {"EventStartDate": {"$lt": "2014-06-29 18:32:00&#46;000"} }, 
         {"EventEndDate": {"$lt": "2014-06-29 18:32:00&#46;000"} } 
     ] 
 }

]
}

/code/pre

Regards.

Sun Jun 15, 2014 8:55 pm

THANKS!!!
Its working GREAT!!!

In case anyone else will have the same Q,
You can replace "and" with "or" and vice versa and than you will get all items in the range (instead the items outside the range)

Thanks Yurii!

Wed Mar 04, 2015 2:55 am

Can anyone tell me how to add time to the date picker? because when I insert a date via date picker into the database it shows something like 2015-03-03 00:00:00.000

Wed Mar 04, 2015 11:39 am

Hi Christian -

DatePicker allows you to select only date.
For selecting time, you will need to find an appropriate jQuery plugin and add it to your project.

Sun Jan 17, 2016 6:29 am

Hi Yurii,

would you please help me to make 'where' code for this type:

start date: before (any date in the past)
end date: Now (today)

I want to show records from today to future (records with the past date don't show)

Best Regards

Mon Jan 18, 2016 6:10 am

thank you I fix it by my self as bellow for everyone who has this problem:

var d = new Date();
d.setDate(d.getDate());
var dateStr = d.toISOString().replace(/T.+/gi, "");

var returnObject = {
"column01": {
"$regex": "search_word" , $options: "i"
},
"column02": {
"$gte": dateStr
}
};

return JSON.stringify(returnObject);

even you can search column01 and filter it with column02(date) to show only rows after today