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!
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
]
}
]
}
]
}
}
}
])
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.