Hey friends, Please i need help, I have a table called ward wit wards details such as name, date of birth and so on. i want to get the data of all the wards that their birthday will be in the next 7days using aggregate. Kindly help. i am stocked
To explain better, i want to get all wards data using where the date_of_birth column will come up in the next 7days.
Thanks
Hello @Gbade_Francis, Welcome to MongoDB Community Forum,
You can use conditional operators with the and condition, here you have to input your current date for greater than and equal to condition and current date +7 days date in less than condition,
"date_of_birth": {
"$gte": <current date>,
"$lt": <current date + 7 days>
}
Thanks for the response. but current date?? current date should be like recent date now? it did not work with the birthday date.
let getbirthday= await model.aggregate([
[
{
'$match': {
'isDeleted': false,
"dateOfBirth": {
"$gte": "$dateOfBirth",
"$lt": "$dateOfBirth"+7
}
}
}, {
'$project': {
'_id': '$_id',
'dateOfBirth': {
'$dateToString': {
'format': '%Y-%m-%d',
'date': '$dateOfBirth'
}
},
'firstName': '$firstName',
'middleName': '$middleName',
'lastName': '$lastName',
'sex': '$sex'
}
}
]
])
Please help me to check.
This is the data from my ward table:
[
{
"sex": "female",
"dateJoined": "2022-02-13T18:58:37.083Z",
"roleName": null,
"isDeleted": false,
"isRestricted": false,
"levelHistory": [],
"_id": "6209599e9ee1d60016315ce0",
"admissionNumber": "Dia001",
"firstName": "kehinde",
"middleName": "",
"lastName": "fatokun",
"dateOfBirth": "2006-02-23T19:18:12.000Z",
"guardianContact": "08034356783",
"currentLevel": "620956889ee1d60016315bfc",
"schoolId": "620956889ee1d60016315bf8",
"__v": 0
},
{
"sex": "male",
"dateJoined": "2022-02-13T18:58:37.083Z",
"roleName": null,
"isDeleted": false,
"isRestricted": false,
"levelHistory": [],
"_id": "620959f09ee1d60016315ced",
"admissionNumber": "Dia002",
"firstName": "funsho ",
"middleName": "",
"lastName": "adeoye",
"dateOfBirth": "2014-02-20T19:19:30.000Z",
"guardianContact": "08035678893",
"currentLevel": "620956889ee1d60016315bfa",
"schoolId": "620956889ee1d60016315bf8",
"__v": 0
},
{
"sex": "female",
"dateJoined": "2022-02-13T18:58:37.083Z",
"roleName": null,
"isDeleted": false,
"isRestricted": false,
"levelHistory": [],
"_id": "62095b199ee1d60016315d0c",
"admissionNumber": "dia003",
"firstName": "tolani",
"middleName": "",
"lastName": "makinde",
"dateOfBirth": "2015-02-11T19:24:34.000Z",
"guardianContact": "08033567898",
"currentLevel": "620956889ee1d60016315bfa",
"schoolId": "620956889ee1d60016315bf8",
"__v": 0
},
{
"sex": "male",
"dateJoined": "2022-02-13T18:58:37.083Z",
"roleName": null,
"isDeleted": true,
"isRestricted": false,
"levelHistory": [],
"_id": "62095ba19ee1d60016315d25",
"admissionNumber": "Dia004",
"firstName": "kayode ",
"middleName": "",
"lastName": "adeolu",
"dateOfBirth": "2010-02-18T19:26:53.000Z",
"guardianContact": "08033679866",
"currentLevel": "620956889ee1d60016315bfa",
"schoolId": "620956889ee1d60016315bf8",
"__v": 0
},
{
"sex": "female",
"dateJoined": "2022-02-13T18:58:37.083Z",
"roleName": null,
"isDeleted": false,
"isRestricted": false,
"levelHistory": [],
"_id": "62095bec9ee1d60016315d31",
"admissionNumber": "dia005",
"firstName": "tina",
"middleName": "",
"lastName": "phillips",
"dateOfBirth": "2010-02-18T19:28:04.000Z",
"guardianContact": "08035678764",
"currentLevel": "620956889ee1d60016315bfa",
"schoolId": "620956889ee1d60016315bf8",
"__v": 0
},
{
"sex": "female",
"dateJoined": "2022-02-13T18:58:37.083Z",
"roleName": null,
"isDeleted": false,
"isRestricted": false,
"levelHistory": [],
"_id": "62095cda9ee1d60016315d46",
"admissionNumber": "dia006",
"firstName": "kehinde",
"middleName": "",
"lastName": "faye",
"dateOfBirth": "2006-02-16T00:00:00.000Z",
"guardianContact": "08143469839",
"currentLevel": "620956889ee1d60016315bfa",
"schoolId": "620956889ee1d60016315bf8",
"__v": 0
},
{
"sex": "male",
"dateJoined": "2022-02-14T07:13:58.502Z",
"roleName": null,
"isDeleted": false,
"isRestricted": true,
"levelHistory": [],
"_id": "620a02689e6dfb00162eaa98",
"admissionNumber": "DI001",
"firstName": "gold",
"middleName": "",
"lastName": "silver",
"dateOfBirth": "2022-02-01T07:18:45.000Z",
"guardianContact": "07037617125",
"currentLevel": "620956889ee1d60016315bfb",
"schoolId": "620956889ee1d60016315bf8",
"__v": 0,
"imageUrl": "http://res.cloudinary.com/asm-web/image/upload/v1646499727/zhes72svpkdwylmzl638.png"
},
]
Thanks
Means a specific date period, right?
so current date means now date, for ex in JS,
let fromDate = new Date();
let toDate = new Date();
toDate.setDate(toDate.getDate() + 7);
toDate = new Date(toDate);
The query would be,
{
"$match": {
"dateOfBirth": {
"$gte": fromDate,
"$lt": toDate
}
}
}
Boos, You are the best. It actually work, but i think there is something needed to be done again.
because i run it, remember we have date of birth which is
2009-10-13T00:00:00.000+00:00, Meaning that we have
year i was born is 2009
Month is 10
and day is 13
Now, the system should calculate in the sense that if it is 2009, 10, 13, how will the system understand that the birthday is comming up on 2022-10-13. because not untill i alter the birthday and change it to todays date before i can get the data. if not, its given empty array.
I hope you understand boss
let fromDate = new Date();
console.log(fromDate)
let toDate = new Date();
console.log(toDate)
toDate.setDate(toDate.getDate() + 7);
toDate = new Date(toDate);
console.log(toDate)
let getbirthday= await model.aggregate([
[
{
'$match': {
'isDeleted': false,
"dateOfBirth": {
"$gte": fromDate,
"$lt": toDate
}
}
]
then i got this
[ ]
GET /api/v1/birthday/get-wardBirthday - - ms - -
2022-07-07T11:16:34.972Z
2022-07-07T11:16:34.973Z
2022-07-14T11:16:34.973Z
[ ]
instead of it to pull the wards birthday that belongs to that range.
remember my date of birth i inserted last year can be 1988-03-13
and i want the system to remember my birthday this year. and it should remind me 7days before my birthday… (This is exactly what it should work boss).
Thanks so much
Okay, I misunderstood,
Might be there are other solutions as well but, I know the way is $expr expression with aggregation operators, by using $month and $dayOfMonth operators,
let fromDate = new Date();
let toDate = new Date();
toDate.setDate(toDate.getDate() + 7);
toDate = new Date(toDate);
let getbirthday= await model.aggregate([
{
"$match": {
"$expr": {
"$and": [
{
"$gte": [
{ "$dayOfMonth": "$dateOfBirth" },
{ "$dayOfMonth": fromDate }
]
},
{
"$gte": [
{ "$month": "$dateOfBirth" },
{ "$month": fromDate }
]
},
{
"$lte": [
{ "$dayOfMonth": "$dateOfBirth" },
{ "$dayOfMonth": toDate }
]
},
{
"$lte": [
{ "$month": "$dateOfBirth" },
{ "$month": toDate }
]
}
]
}
}
}
]);
You can simplify more this by passing the month number and date number instead of the whole date,
let fromMonth = 7;
let toMonth = 7;
let fromDay = 7;
let toDay = 14;
let getbirthday= await model.aggregate([
{
"$match": {
"$expr": {
"$and": [
{
"$gte": [
{ "$dayOfMonth": "$dateOfBirth" },
fromDay
]
},
{
"$gte": [
{ "$month": "$dateOfBirth" },
fromMonth
]
},
{
"$lte": [
{ "$dayOfMonth": "$dateOfBirth" },
toDay
]
},
{
"$lte": [
{ "$month": "$dateOfBirth" },
toMonth
]
}
]
}
}
}
]);
Wow!!!. I am grateful. it work exactly the way i want it. Thanks so much and thanks so much. CHeers.
Please i will like to be your friend, you can send me your whatsap number on +2348132185887 or in my email. gbade.francis@gmail.com
I am not too sure that this will work for boundary cases.
Assume you want birthdays from June 30th to July 6th.
So your fromDay=30, fromMonth=6, toDay=6, toMonth=7.
There is no way you could find a day that $gte:fromDay and $lte:toDay. The aggregation works because fromMonth == toMonth.
You need something a little bit more complex.
Something like:
if fromMonth == toMonth
return monthOfBirth == fromMonth && fromDay <= dayOfBirth && dayOfBirth <= toDay
else if monthOfBirth == fromMonth
return fromDay <= dayOfBirth
else if monthOfBirth == toMonth
return dayOfBirth <= toDay
Which would look like:
/* set stage to set monthOfBirth and dayOfBirth using $month and $dayOfMonth */
"$or" : [
{ "$and" : [
{ "$eq" : [ fromMonth , toMonth ] } ,
{ "$eq" : [ fromMonth , "$monthOfBirth" ] } ,
{ "$lte" : [ fromDay , "$dayOfBirth" ] } ,
{ "$lte" : [ "$dayOfBirth" , toDay ] } ,
] } ,
{ "$and" : [
{ "$eq" : [ fromMonth , "$monthOfBirth" ] } ,
{ "$lte" : [ fromDay , "$dayOfBirth" ] }
] } ,
{ "$and" : [
{ "$eq" : [ toMonth , "$monthOfBirth" ] } ,
{ "$lte" : [ "$dayOfBirth" , toDay ] } ,
] }
]
I am still unsure if the above handle the edge cases where fromMonth=12 and toMonth=1
1 Like
You are right,
There is another solution, I am not sure about the performance but, what if we fix the year and match the condition, and reconstruct the date by $dateFromParts operator,
let fixedYear = 2000;
let fromDate = new Date();
fromDate.setFullYear(fixedYear);
fromDate = new Date(fromDate);
let toDate = new Date();
toDate.setFullYear(fixedYear);
toDate.setDate(toDate.getDate() + 7);
toDate = new Date(toDate);
let getbirthday= await model.aggregate([
{
"$match": {
"$expr": {
"$and": [
{
"$gte": [
{
"$dateFromParts": {
"year": fixedYear,
"month": { "$month": "$dateOfBirth" },
"day": { "$dayOfMonth": "$dateOfBirth" }
}
},
fromDate
]
},
{
"$lt": [
{
"$dateFromParts": {
"year": fixedYear,
"month": { "$month": "$dateOfBirth" },
"day": { "$dayOfMonth": "$dateOfBirth" }
}
},
toDate
]
}
]
}
}
}
])
This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.