Mongoose find the difference between 2 dates

Question

im trying to calculate the difference between endDate and startDate for every document in vacations collection to find How many vacation days does the employee have? :

  const employee = await Employee.findOne({ nameAR: req.body.name });

  const vacations = await Vacation.find({ employeeId: employee._id });
  let numberOfDays = 0;

  vacations.map((vacation) => {

    Vacation.aggregate([
      {
        $project: {
          _id: vacation._id,
          start: vacation.startDate,
          end: vacation.endDate,
          result: {
            $subtract: ["$end", "$start"],
          },
        },
      },
    ]).exec(function (err, diff) {
      vacationCount.numberOfDays = vacationCount.numberOfDays + diff;
    });
  });

map loops vacations array for an employee and aggregate to find the difference between dates for every vacation document, store it in variable numberOfDay and add the next one.

i have 2 problem here : first : exec method returns an array of objects, which i dont want, i want it only to return the result because i dont want to map again

second and big one : result returns null which means aggregate doesn't even work properly

i've made sure employee and vacations data fetches succesfully

note: startDate and endDate could be null in some docs.

vacations returned docs will be something like this :

[
    {
        "_id": "631b59a8f25e201029953b31",
        "employeeId": "631b595578dae2d46f112389",
        "type": "Annual",
        "startDate": "2022-09-09T00:00:00.000Z",
        "endDate": "2022-09-14T00:00:00.000Z",
        "numberOfHours": null,
        "status": "approved",
        "__v": 0
    },
    {
        "_id": "631b59d9f25e201029953b3d",
        "employeeId": "631b595578dae2d46f112389",
        "type": "Day time leave",
        "startDate": null,
        "endDate": null,
        "numberOfHours": "4",
        "status": "approved",
        "__v": 0
    },
    {
        "_id": "631b59dbf25e201029953b40",
        "employeeId": "631b595578dae2d46f112389",
        "type": "Day time leave",
        "startDate": null,
        "endDate": null,
        "numberOfHours": "5",
        "status": "approved",
        "__v": 0
    },
]

thanks in advance

Answer 1

Try this Out and do let me know if it works for you:

// Finding particular Employee details from employee collection
const employee = await Employee.findOne({ nameAR: req.body.name });

// Stores Total Number of Vacation Days 
let numberOfDay  = 0;

/* Finding all matching vacation records from vaction collection,aggregate them 
and calculate no. of days between end and start date, stores it to numberOfVacationDays.*/
const vacations = await Vacation.aggregate([
  {
    $project: {
      _id: "$_id",
      start: "$startDate",
      end: "$endDate",
      numberOfVacationDays: {
        $divide: [
          {
            $subtract: [
              {
                "$toDate": "$endDate"
              },
              {
                "$toDate": "$startDate"
              }
            ]
          },
          //returned mills can be converted to day by dividing by 86400000 (number of milliseconds in 24 hours)
          86400000 
        ]
      },
    },
  },

  {
    "$match": {
      employeeId: employee._id 
    }
  }

]).exec();

// Once we have all vacation days of employee ,
//we can calculate total Number Of Vacation Days and store it in numberOfDay
vacations.forEach((vacation) =>{
    if(vacation.numberOfVacationDays){
        numberOfDay  += vacation.numberOfVacationDays;
    }
});