'How to subtract two date time in mongodb

I have used aggregate function.

db.checkins.aggregate([
       {$match: {checkinType: "Beacon",
               "associationIds.organizationId":"af39bc69-1938-4149",
               "checkinData.time": {"$gte": new Date("2018-01-18T18:30:00.000Z"), 
                                   "$lt": new Date("2018-01-19T18:30:00.000Z")}
                }
        },
       {"$sort":{"checkinData.time":-1}},
       {$group: {"_id":
                    {"orgId":"$asst.organizationId", "userId":"$asst.userId"},
                    "lastSeen":{"$first":"$checkinData.time"},
                   "firstSeen":{"$last":"$checkinData.time"},
               }
       }, 
      {"$project":{"_id":1,"lastSeen":1, "firstSeen":1, 
                  totalHourSpent:{$subtract: ["$lastSeen","$firstSeen"]}}}, 
  ])

When I performed this Query mongo return totalHourSpent in milisecond which is given below.

{
  "_id" : {
        "orgId" : "af39bc69-1938-4149-b9f7-f101fd9baf73",
        "userId" : "34adb4a0-0012-11e7-bf32-cf79d6b423e9"
  },
 "lastSeen" : ISODate("2018-01-19T18:49:52.242+05:30"),
 "firstSeen" : ISODate("2018-01-19T10:08:21.026+05:30"),
 "totalHourSpent" : NumberLong("31291216")
},
{
  "_id" : {
       "orgId" : "af39bc69-1938-4149-b9f7-f101fd9baf73",
       "userId" : "679416b0-3f88-11e7-8d27-77235eb1ba9b"
   },
   "lastSeen" : ISODate("2018-01-19T20:51:30.946+05:30"),
   "firstSeen" : ISODate("2018-01-19T11:07:44.256+05:30"),
   "totalHourSpent" : NumberLong("35026690")
 },

How to calculate totalHourSpent in hour. Thanks in advance.

node.jsmongodbaggregatemlab


Solution 1:[1]

group, count, avg

subtract value return in milliseconds

  const avgTime = await Post.aggregate<{
    count?: string;
    avg_time: string;
  }>([
    {
      $match: {
        finishedAt: {
          $exists: true,
        },
      },
    },

    {
      $group: {
        _id: null,
        count: {
          $sum: 1,
        },
        avg_time: {
          $avg: {
            // $subtract: [
            //   {
            //     $ifNull: ['$finishedAt', 0],
            //   },
            //   {
            //     $ifNull: ['$createdAt', 0],
            //   },
            // ],
            $subtract: ['$finishedAt', '$createdAt'],
            //$subtract: [{ $toDate: '$finishedAt' }, { $toDate: '$createdAt' }],
          },
        },
      },
    },
  ]);

for more info

https://database.guide/mongodb-subtract/#:~:text=In%20MongoDB%2C%20you%20can%20use,and%20return%20the%20resulting%20date

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Rafiq

Related Questions