How to fetch the data btwn two diff date and time in MongoDB

[
      {
        "key":"2",
        "start_date": "2023-01-03T03:30:00.000Z",
        "end_date": "2023-01-03T05:30:00.000Z",
        
      },
      {"key":"1",
        "start_date": "2023-01-04T03:30:00.000Z",
        "end_date": "2023-01-04T05:30:00.000Z",
        
      },
      {
        "key":"1",
        "start_date": "2023-01-05T03:30:00.000Z",
        "end_date": "2023-01-05T05:30:00.000Z",
        
      }
    ]

    db.collection.find({
      {"key":"2"},
      $or: [
        {
          start_date: {
            $gte: "2023-01-04T04:30:00.000Z",
            $lte: "2023-01-04T05:00:00.000Z"
          }
        },
        {
          end_date: {
            $lte: "2023-01-04T05:00:00.000Z",
            $gte: "2023-01-04T04:30:00.000Z"
          }
        }
      ]
    })

I have two collection in database Monday 10am to 11pm Tuesday 10am to 11pm Friday 11am to 12pm how I will get the result if i find the Monday 10:30am to 10:45am, i want the result for btwn time also. Query should return -

    [{
        "key":"2",
        "start_date": "2023-01-04T03:30:00.000Z",
        "end_date": "2023-01-04T05:30:00.000Z",
        
      }]

CodePudding user response：

You start date for key:2 is "2023-01-03T03:30:00.000Z and end date is 2023-01-03T05:30:00.000Z so you can not get that record if your query for the

$or: [
    {
      start_date: {
        $gte: "2023-01-04T04:30:00.000Z",
        $lte: "2023-01-04T05:00:00.000Z"
      }
    },
    {
      end_date: {
        $lte: "2023-01-04T05:00:00.000Z",
        $gte: "2023-01-04T04:30:00.000Z"
      }
    }
  ]

This is clearly not matching the filter you are applying you have to start your date from "2023-01-03T03:30:00.000Z" and also please use $and operator. $or operator will match any of the start date or end dates. but if you want to get records that match both condition you have to use $and.

CodePudding user response：

This is not an answer, but it is a bit difficult to put it as comment.

You need to define clearly the condition what "between two diff date and time" means. When your data has start_date and end_date and your query also have a date range (i.e. start and end) then you can have 6 different conditions. This table visualizes them:

          start_date         end_date         
    ------------------------------------------> time
(1)  x     x   |                |             
(2)        x   |           x    |             
(3)            |     x     x    |             
(4)            |     x          |    x        
(5)        x   |                |    x        
(6)            |                |    x    x   

You need to define which of them are considered as true and false. Condition (1) and (6) are certainly false but what about the others?

In advanced case, you also have to consider documents where start_date or end_date is missing.

          start_date       undefined
    ------------------------------------------
(7)  x     x   |                             
(8)        x   |           x                 
(9)            |     x     x                 


           undefined         end_date         
    ------------------------------------------
(10)  x     x                   |             
(11)        x                   |    x        
(12)                            |    x    x