How to compare 2 date ranges in DB collection

[Yaniv]

In my DB collection (Appery based on Mongo DB) I have startDate and EndDate columns (date range).
Before I want to insert new record to my collection I want to check if my new date range overlap the existing data range in table, so in case its not I will create new record.
Let say that in collection I have the following record:
StartDate = 2014-06-29 18:00:00.000
EndtDate = 2014-06-29 22:00:00.000

Now I want to add new record that:
StartDate = 2014-06-29 16:00:00.000
EndtDate = 2014-06-29 19:00:00.000
and in this case I don't want to create new record since this overlap the existing data in DB

*There are 4 combination to overlap and two that not
Overlap:

1. Inside the range
   StartDate = 2014-06-29 19:00:00.000
   EndtDate = 2014-06-29 20:00:00.000

2. All over the range
   StartDate = 2014-06-29 17:00:00.000
   EndtDate = 2014-06-29 23:00:00.000

3. before and after the start date only:
   StartDate = 2014-06-29 17:00:00.000
   EndtDate = 2014-06-29 19:00:00.000

4. before and after the end date only:
   StartDate = 2014-06-29 19:00:00.000
   EndtDate = 2014-06-29 23:00:00.000

   In addition:
   *There are 2 combination to events that not overlap existing dates:

5. All date range before StartDate
   StartDate = 2014-06-29 12:00:00.000
   EndtDate = 2014-06-29 17:00:00.000

6. All date range after EndDate
   StartDate = 2014-06-29 23:00:00.000
   EndtDate = 2014-06-29 23:30:00.000

   How can we do this SQL to validate all combination above?

[Yurii Orishchuk]

Hi Yaniv.

You need to send "query" request to your collection with the following "where":

precode

{
"$or": [
{"$and": [ {"StartDate": {"$gt": "2018-06-05 00:00:00.000"} }, {"EndDate": {"$gt": "2018-06-05 00:00:00.000"} } ] },
{"$and": [ {"StartDate": {"$lt": "2018-06-05 00:00:00.000"} }, {"EndDate": {"$lt": "2018-06-05 00:00:00.000"} } ] }
]
}

/code/pre

This "where" clause will select you only rows with these two cases when your current dates do not overlap rows in your collection.

Then on success event you should compare count of returned items and make decision about this validation.

Regards.

[Yaniv]

Hi Yurii,
Currently the query return inconsistent results,
While using above query I'm getting that those dates exists while they not...
Here are few combination that I test with the new query and their results:
In DB I have the following records:
Item1:
StartDate = 2014-06-29 18:30:00.000
EndtDate = 2014-06-29 22:00:00.000

Item2:
StartDate = 2014-06-29 10:00:00.000
EndtDate = 2014-06-29 14:20:00.000

Now I want to check if the following date range exists using the query:

1. Not exist in the range:
   StartDate = 2014-06-29 16:00:00.000
   EndtDate = 2014-06-29 17:00:00.000

   Result: For this I'm getting both items.

2. Fully exist in the range (means start AND end date inside the range):
   StartDate = 2014-06-29 19:30:00.000
   EndtDate = 2014-06-29 20:00:00.000

   Result: For this I'm getting the items that NOT in the range (means Item2)

3. Fully exist in the range (means start AND end date inside the range):
   StartDate = 2014-06-29 11:30:00.000
   EndtDate = 2014-06-29 13:00:00.000

   Result: For this I'm getting the items that NOT in the range (means Item1)

4. Only one date in range (means start OR end date inside the range):
   StartDate = 2014-06-29 11:30:00.000
   EndtDate = 2014-06-29 13:00:00.000

   Result: For this I'm getting both items.

5. Start date exist in one range and End date exists in other range:
   StartDate = 2014-06-29 11:30:00.000
   EndtDate = 2014-06-29 19:00:00.000

   Result: For this I'm getting both items.

   My expectation is that in case the queried date range not exists I will get a result that will indicate that this date range not exists (So I will know that this date range not exists in DB and I can create new record with it).

   Can you please check

[Yurii Orishchuk]

Hi Yaniv.

Please provide us screen shots of your DB and "test" tab of service with "where" you you have tryed.

Also please share your DB with a href="mailto:support@appery.io" rel="nofollow"support@appery.io/a and tell it name.

Regards.

[Yaniv]

Hi Yurii,
DB shared:
DB name: iMenu,
Collection name:EventsDetails,
Columns Name: EventStartDate, EventEndDate

You can run the query from the app service itself, using the Test tab (app shared):
App Name: tikMenu,
Service Name: "iMenuDB_EventsDetails_query_service"

In order to test:
Use the same query that you provided, while changing the columns name and use the testing input based on the combination that wrote in my second replay
i.e.
{
"$or": [
{"$and": [ {"EventStartDate": {"$gt": "2014-06-29 16:00:00.000"} }, {"EventEndDate": {"$gt": "2014-06-29 17:00:00.000"} } ] },
{"$and": [ {"EventStartDate": {"$lt": "2014-06-29 16:00:00.000"} }, {"EventEndDate": {"$lt": "2014-06-29 17:00:00.000"} } ] }
]
}

i.e.

{
"$or": [
{"$and": [ {"EventStartDate": {"$gt": "2014-06-29 19:30:00.000"} }, {"EventEndDate": {"$gt": "2014-06-29 20:00:00.000"} } ] },
{"$and": [ {"EventStartDate": {"$lt": "2014-06-29 19:30:00.000"} }, {"EventEndDate": {"$lt": "2014-06-29 20:00:00.000"} } ] }
]
}

*Keep in mind that column name are EventStartDate, EventEndDate (and not StartDate, EndDate)

Thanks

[Evgene Karachevtsev]

Hello Yaniv,

As far as we could understand you can use next query:

code{"$or": [{"EventStartDate": {"$gt": "You check End Date"} }, {"EventEndDate": {"$lt": "You check StartDate"} }]} /code

If this query return emty response you can insert row

[Yaniv]

While using this query I'm getting that event exists event while the date and time that are free.

Let me try to add more info here.
This collection contains list of events, each event has start date & time and end date & time.
While creating new event I need to check that the new added event not overlap (contains/included/partial included) the existing events that exists in the DB.

So in case I have already the following events registered

1. 10 Jun from 18:30 - 22:00

2. 10 Jun from 12:00 - 15:30

   Before adding the new event I need to make sure that this event not occurs in one of this ranges,
   so in case the new event will be on 10 Jun from 16:00 - 17:30 - this will be OK and I will be able to add it but in case the event will be on 10 Jun from 14:00 - 17:30 - this means that there is already existing event at this time and need to search other available time.
   I need your assistance to build the right query to check that the new event date&time are really available and not overlapping (contains/included/partial included) existing events.

   Thanks

[Evgene Karachevtsev]

Yaniv,

You can use for example this query:

code [{"EventStartDate": {"$gt": "You check End Date"} }, {"EventEndDate": {"$lt": "You check StartDate"} }]} /code

But before this request get whole count rows in collection. If it is equal to count of rows returned the query you can add row. Or you can make up another way to check the conditions

[Yaniv]

While using above query with date&time that available:
EventStartDate= 2014-06-29 16:00:00.000
EventEndDate= 2014-06-29 17:00:00.000
NO RESULT returned (means that the # of row in the collection and the amount of rows in the query is different so this indicate that this date&time not available - while it is available).

While using above query with date&time that exist already
EventStartDate= 2014-06-29 19:30:00.000
EventEndDate= 2014-06-29 20:00:00.000
NO RESULT returned (means that the # of row in the collection and the amount of rows in the query is different so this indicate that this date&time not available

Anyhow - since both scenario return the SAME response for different cases this indicate that additional/different query is required.

[Yurii Orishchuk]

Hi Yaniv.

Ok i've tested your DB and your query service.

You have 3 items in your collection:

precode

1 2014-06-29 10:00:00.000 - 2014-06-29 14:20:00.000

2 2014-06-29 18:30:00.000 - 2014-06-29 22:00:00.000

3 2014-06-29 18:30:00.000 - 2014-06-29 22:00:00.000

/code/pre

So here is a tests:

1 test:

Test date:
2014-06-29 11:00:00.000 - 2014-06-29 12:00:00.000 - not overlaped with 2nd and 3rd items.

request would be:

precode

{
"$or": [
{"$and": [ {"EventStartDate": {"$gt": "2014-06-29 11:00:00.000"} }, {"EventEndDate": {"$gt": "2014-06-29 12:00:00.000"} } ] },

Code: Select all

 {"$and": [ {"EventStartDate": {"$lt": "2014-06-29 11:00:00.000"} }, {"EventEndDate": {"$lt": "2014-06-29 12:00:00.000"} } ] }  

]
}

/code/pre

And it should return 2 items: http://prntscr.com/3rn5ls/direct

Passed OK.

2 test:

Test date:
2014-06-29 14:21:00.000 - 2014-06-29 14:22:00.000 - not overlaped with all three items.

request would be:

precode

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

Code: Select all

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

]
}

/code/pre

And it should return 3 items: http://prntscr.com/3rn6wi/direct

Passed OK.

3 test:

Test date:
2014-06-29 18:31:00.000 - 2014-06-29 18:32:00.000 - not overlaped with 1st item.

request would be:

precode

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

Code: Select all

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

]
}

/code/pre

And it should return 1 item: http://prntscr.com/3rn7xk/direct

Passed OK.

Also i've tested other test cases and all was passed ok.

Note: If you want to see only "count" without all items information please add to your request parameter "count" with value "1" http://prntscr.com/3rn8ky/direct

Regards.