MongoDB计算子文档日期差并求和用户总培训时长

First, let’s assume your user documents have a trainings array where each entry includes startDate (ISODate) and endDate (ISODate or null for ongoing training). Here’s how to implement both of your requirements using MongoDB’s aggregation framework:

Step 1: Calculate Duration for Each Training Segment #

We’ll process each segment individually, handle ongoing training by using the current date, and compute exact year/month/day durations.

Aggregation Pipeline for Individual Segment Durations #

db.users.aggregate([
  // Unwind the trainings array to process each segment separately
  { $unwind: "$trainings" },
  
  // Replace null endDate with current date for ongoing training
  {
    $addFields: {
      "trainings.endDate": {
        $ifNull: ["$trainings.endDate", new Date()]
      }
    }
  },
  
  // Calculate total months between start and end date
  {
    $addFields: {
      "trainings.totalMonths": {
        $dateDiff: {
          startDate: "$trainings.startDate",
          endDate: "$trainings.endDate",
          unit: "month"
        }
      }
    }
  },
  
  // Split total months into year and month components
  {
    $addFields: {
      "trainings.duration": {
        year: { $floor: { $divide: ["$trainings.totalMonths", 12] } },
        month: { $mod: ["$trainings.totalMonths", 12] }
      }
    }
  },
  
  // Calculate exact day component by aligning start date to end date's year/month
  {
    $addFields: {
      "trainings.duration.day": {
        $dateDiff: {
          startDate: {
            $dateAdd: {
              startDate: "$trainings.startDate",
              unit: "month",
              amount: "$trainings.totalMonths"
            }
          },
          endDate: "$trainings.endDate",
          unit: "day"
        }
      }
    }
  },
  
  // Group back into user documents with updated training segments
  {
    $group: {
      _id: "$_id",
      name: { $first: "$name" }, // Include other user fields as needed
      trainings: { $push: {
        startDate: "$trainings.startDate",
        endDate: "$trainings.endDate",
        duration: "$trainings.duration"
      }}
    }
  }
])

This will return each user with their trainings array updated to include a duration object (e.g., { year: 2, month: 6, day: 12 }) for every segment.

Step 2: Calculate Total Training Duration #

Summing exact year/month/day durations requires handling rollover (e.g., 35 days = 1 month + 5 days). Since month lengths vary, we’ll use two approaches: an approximate rollup, and a precise millisecond-based total.

Option 1: Approximate Total with Rollover #

Add these stages to the end of the pipeline above to sum and roll up duration components:

// Sum individual duration components
{
  $addFields: {
    totalYears: { $sum: "$trainings.duration.year" },
    totalMonths: { $sum: "$trainings.duration.month" },
    totalDays: { $sum: "$trainings.duration.day" }
  }
},

// Roll up days into months (assuming 30 days/month)
{
  $addFields: {
    rolledUpMonths: { $add: ["$totalMonths", { $floor: { $divide: ["$totalDays", 30] } }] },
    remainingDays: { $mod: ["$totalDays", 30] }
  }
},

// Roll up months into years
{
  $addFields: {
    totalDuration: {
      year: { $add: ["$totalYears", { $floor: { $divide: ["$rolledUpMonths", 12] } }] },
      month: { $mod: ["$rolledUpMonths", 12] },
      day: "$remainingDays"
    }
  }
},

// Clean up unnecessary fields (optional)
{
  $project: {
    name: 1,
    trainings: 1,
    totalDuration: 1
  }
}

Option 2: Precise Total Using Milliseconds #

This method calculates total duration in milliseconds first, then converts to approximate years/months/days:

// Add duration in milliseconds for each segment
{
  $addFields: {
    "trainings.durationMs": {
      $subtract: ["$trainings.endDate", "$trainings.startDate"]
    }
  }
},

// Sum all milliseconds and convert to total days
{
  $group: {
    _id: "$_id",
    name: { $first: "$name" },
    trainings: { $push: "$trainings" },
    totalDurationMs: { $sum: "$trainings.durationMs" }
  }
},
{
  $addFields: {
    totalDays: {
      $dateDiff: {
        startDate: new Date(0),
        endDate: { $add: [new Date(0), "$totalDurationMs"] },
        unit: "day"
      }
    }
  }
},

// Convert total days to year/month/day approximation
{
  $addFields: {
    totalDuration: {
      year: { $floor: { $divide: ["$totalDays", 365] } },
      month: { $floor: { $divide: [{ $mod: ["$totalDays", 365] }, 30] } },
      day: { $mod: [{ $mod: ["$totalDays", 365] }, 30] }
    }
  }
}

Notes #

• Individual segment durations are precise, as MongoDB’s $dateDiff and $dateAdd account for leap years and varying month lengths.
• Total duration sums use approximations because there’s no universal definition of a "month" (30 days is a common baseline). Adjust the 30-day/month or 365-day/year values if you need different rounding rules.

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