如何在MongoDB中按employeeId汇总员工工作时长？

问题背景 #

需要按employeeId汇总员工的总工作时长，例如集合中第3条和第5条记录属于同一个employeeId（63fefac1ce76e418acc00ee7），需计算该员工的累计工作时长。尝试使用Mongoose的aggregate聚合功能实现，但未成功。当前接口仅返回按天统计的单条记录，无法跨天汇总。

集合数据 #

[
  {
    _id: new ObjectId("64036f7ac0829fe25c6ef435"),
    clockIn: 2023-03-04T06:28:06.433Z,
    clockOut: 2023-03-04T10:30:08.601Z,
    employeeId: ObjectId("63feed3c60a04fbc61c52f91"),
    createdAt: 2023-03-04T16:19:06.436Z,
    updatedAt: 2023-03-04T16:19:08.601Z,
    __v: 0
  },
  {
    _id: new ObjectId("64036f8fc0829fe25c6ef43b"),
    clockIn: 2023-03-04T06:27:27.013Z,
    clockOut: 2023-03-04T15:02:28.142Z,
    employeeId: ObjectId("63feece460a04fbc61c52f89"),
    createdAt: 2023-03-04T16:19:27.014Z,
    updatedAt: 2023-03-04T16:19:28.142Z,
    __v: 0
  },
  {
    _id: new ObjectId("64036fb0c0829fe25c6ef442"),
    clockIn: 2023-03-04T06:32:00.570Z,
    clockOut: 2023-03-04T15:01:01.676Z,
    employeeId: ObjectId("63fefac1ce76e418acc00ee7"),
    createdAt: 2023-03-04T16:20:00.571Z,
    updatedAt: 2023-03-04T16:20:01.677Z,
    __v: 0
  },
  {
    _id: new ObjectId("64036fd3c0829fe25c6ef448"),
    clockIn: 2023-03-04T07:35:35.630Z,
    clockOut: 2023-03-04T16:49:27.038Z,
    employeeId: ObjectId("63fef8c7ce76e418acc00e98"),
    createdAt: 2023-03-04T16:20:35.631Z,
    updatedAt: 2023-03-04T16:49:27.044Z,
    __v: 0
  },
  {
    _id: new ObjectId("64037df808650d277693f106"),
    clockIn: 2023-03-03T06:31:56.290Z,
    clockOut: 2023-03-03T15:03:58.632Z,
    employeeId: ObjectId("63fefac1ce76e418acc00ee7"),
    createdAt: 2023-03-04T17:20:56.302Z,
    updatedAt: 2023-03-04T17:20:58.633Z,
    __v: 0
  }
]

当前接口代码 #

timesheetRouter.get('/timesheet/:clockIn/:clockOut/show', async (req, res) => {
  try {
    const clockInOutTimesheet = await Timesheet.find().populate('employeeId', 'name employeeCode department fulltime')
    return res.status(200).json(clockInOutTimesheet)
  } catch (error) {
    console.log(error)
    return res.status(500).json({message: "Error code 1001"})
  }
})

期望结果 #

employeeId: 63feed3c60a04fbc61c52f91 ==> 4.03 hours
employeeId: 63feece460a04fbc61c52f89 ==> 8.08 hours
employeeId: 63fefac1ce76e418acc00ee7 ==> (7.98 + 8.03) = 16.01 hours
employeeId: 63fef8c7ce76e418acc00e98 ==> 8.73 hours

解决方案 #

使用Mongoose的aggregate聚合管道，按以下步骤实现：

修改后的接口代码 #

timesheetRouter.get('/timesheet/:clockIn/:clockOut/show', async (req, res) => {
  try {
    const aggregatedResult = await Timesheet.aggregate([
      // 计算每条打卡记录的工作时长（转换为小时）
      {
        $addFields: {
          workHours: {
            $divide: [
              { $subtract: ["$clockOut", "$clockIn"] },
              1000 * 60 * 60 // 毫秒转小时：1秒=1000毫秒，1小时=3600秒
            ]
          }
        }
      },
      // 按employeeId分组，累加总时长
      {
        $group: {
          _id: "$employeeId",
          totalHours: { $sum: "$workHours" },
          // 可选：保留该员工的所有打卡记录
          timeEntries: { $push: { clockIn: "$clockIn", clockOut: "$clockOut" } }
        }
      },
      // 关联员工集合，获取员工基础信息（和原代码的populate效果一致）
      {
        $lookup: {
          from: "employees", // 替换为你的员工集合实际名称
          localField: "_id",
          foreignField: "_id",
          as: "employee"
        }
      },
      // 格式化输出：保留两位小数，整理字段结构
      {
        $addFields: {
          totalHours: { $round: ["$totalHours", 2] },
          employeeId: "$_id",
          employee: { $arrayElemAt: ["$employee", 0] } // 取出数组中的唯一员工对象
        }
      },
      // 移除不需要的字段
      {
        $project: {
          _id: 0
        }
      }
    ]);

    return res.status(200).json(aggregatedResult);
  } catch (error) {
    console.log(error);
    return res.status(500).json({ message: "Error code 1001" });
  }
});

关键说明 #

1. $addFields阶段：通过$subtract计算clockOut与clockIn的时间差（毫秒），再除以3600000转换为小时，得到单条记录的工作时长。
2. $group阶段：以employeeId为分组键，用$sum累加所有工作时长，得到员工总时长。
3. $lookup阶段：替代原代码的populate，关联员工集合获取详细信息，确保返回结果包含员工的名称、部门等字段。
4. $round操作：将总时长保留两位小数，和期望结果的格式一致。

返回结果示例 #

[
  {
    "totalHours": 4.03,
    "timeEntries": [
      {
        "clockIn": "2023-03-04T06:28:06.433Z",
        "clockOut": "2023-03-04T10:30:08.601Z"
      }
    ],
    "employee": {
      "name": "员工A",
      "employeeCode": "EMP001",
      "department": "技术部",
      "fulltime": true
    },
    "employeeId": "63feed3c60a04fbc61c52f91"
  },
  {
    "totalHours": 16.01,
    "timeEntries": [
      {
        "clockIn": "2023-03-04T06:32:00.570Z",
        "clockOut": "2023-03-04T15:01:01.676Z"
      },
      {
        "clockIn": "2023-03-03T06:31:56.290Z",
        "clockOut": "2023-03-03T15:03:58.632Z"
      }
    ],
    "employee": {
      "name": "员工B",
      "employeeCode": "EMP002",
      "department": "运营部",
      "fulltime": true
    },
    "employeeId": "63fefac1ce76e418acc00ee7"
  }
]

内容的提问来源于stack exchange，提问作者Weslley Fortunato