CODEWITHSUNDEEP
mongodbmongodb-querynosqlnosql-aggregation
briano93
briano93

Reputation: 51

MongoDB - How to get a date that is between two dates

I am creating a database for a dentist. On Tuesdays, the secretary sends reminders to all the patients who have an appointment for the next week.

The query I used in SQL is:

SELECT P.Name    
    , P.Tel_No    
    , A.Appointment_Time    
    , A.Appointment_Date    
FROM Patient AS P    
    INNER JOIN Booking AS B ON P.Patient_ID = B.Patient_ID    
    INNER JOIN Appointment AS A ON B.Appointment_ID = A.Appointment_ID    
WHERE A.Reminder_Sent = 0  
AND A.Appointment_Date BETWEEN     
    ADDDATE(DAY, 6, CURDATE()) AND ADDDATE(DAY, 9, CURDATE());

I want to follow the same logic as the last two lines. So Tuesday plus 6 days is the following Monday, and Tuesday plus 9 days is the following Friday. If the appointment falls between these two dates, then a reminder is sent.

How do I extend this logic to MongoDB?

I imagine it would be something like:

"Appointment Time" : $range{

{$dateAdd:{currentdate(),unit:day,amount:6}, 

{$dateAdd:{currentdate(),unit:day,amount:9}}

Or something like this. Any help would be appreciated!

Upvotes: 1

Views: 1368

Answers (1)

Tom Slabbaert
Tom Slabbaert

Reputation: 22276

You can use the $$NOW variable, like so:

db.collection.aggregate([
  {
    $match: {
      $expr: {
        $and: [
          {
            $gt: [
              "$Appointment_Date",
              {
                $add: [
                  "$$NOW",
                  518400000 // 6 days in miliseconds
                ]
              }
            ]
          },
          {
            $lt: [
              "$Appointment_Date",
              {
                $add: [
                  "$$NOW",
                  777600000 // 9 days in miliseconds
                ]
              }
            ]
          }
        ]
      }
    }
  }
])

Mongo Playground

If you're using Mongo version 5+ this query can be simplified by using $dateAdd, I just assumed you're not on such an advanced version.

Upvotes: 1

Related Questions