Question 115830 in Mongodb Question posted in
The official documentation can be found here.

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

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",
        
      }]
Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search