通过

$dateDiff

运算符 $dateDiff 计算各种单位（如年、月、日等）中的两个日期之间的差异。它可用于确定数据集中两个时间戳之间的持续时间。

语法

$dateDiff: {
   startDate: <expression>,
   endDate: <expression>,
   unit: <string>,
   timezone: <string>, // Optional
   startOfWeek: <string> // Optional
}

参数

参数 说明
startDate 计算的开始日期。
endDate 计算的结束日期。
unit 测量差异的时间单位。 有效值包括：year、、、quarter、monthweekday、hourminute、。 secondmillisecond
timezone 可选。 用于计算的时区。
startOfWeek 可选。 一周的开始日。 有效值为：Sunday、、、Monday、TuesdayWednesdayThursday、。 FridaySaturday

示例

让我们了解 stores 数据集中的示例 json 的用法。

{
  "_id": "e6410bb3-843d-4fa6-8c70-7472925f6d0a",
  "name": "Relecloud | Toy Collection - North Jaylan",
  "location": {
    "lat": 2.0797,
    "lon": -94.4134
  },
  "staff": {
    "employeeCount": {
      "fullTime": 7,
      "partTime": 4
    }
  },
  "sales": {
    "salesByCategory": [
      {
        "categoryName": "Educational Toys",
        "totalSales": 3299
      }
    ],
    "revenue": 3299
  },
  "promotionEvents": [
    {
      "eventName": "Massive Markdown Mania",
      "promotionalDates": {
        "startDate": {
          "Year": 2024,
          "Month": 9,
          "Day": 21
        },
        "endDate": {
          "Year": 2024,
          "Month": 9,
          "Day": 29
        }
      },
      "discounts": [
        {
          "categoryName": "Remote Control Toys",
          "discountPercentage": 6
        },
        {
          "categoryName": "Building Sets",
          "discountPercentage": 21
        }
      ]
    }
  ],
  "company": "Relecloud",
  "city": "North Jaylan",
  "lastUpdated": {
    "$timestamp": {
      "t": 1733313006,
      "i": 1
    }
  },
  "storeOpeningDate": "2024-09-05T11:50:06.549Z"
}

示例 1：计算两个日期之间的天数

查询用于 $dateDiff 计算两个日期字段之间的单位数（例如天、月）。 它有助于测量自给定日期以来的事件长度或时间等持续时间。

db.stores.aggregate([
  { $match: { _id: "e6410bb3-843d-4fa6-8c70-7472925f6d0a" } },
  { $unwind: "$promotionEvents" },
  {
    $project: {
      eventName: "$promotionEvents.eventName",
      startDate: {
        $dateFromParts: {
          year: "$promotionEvents.promotionalDates.startDate.Year",
          month: "$promotionEvents.promotionalDates.startDate.Month",
          day: "$promotionEvents.promotionalDates.startDate.Day"
        }
      },
      endDate: {
        $dateFromParts: {
          year: "$promotionEvents.promotionalDates.endDate.Year",
          month: "$promotionEvents.promotionalDates.endDate.Month",
          day: "$promotionEvents.promotionalDates.endDate.Day"
        }
      },
      durationInDays: {
        $dateDiff: {
          startDate: {
            $dateFromParts: {
              year: "$promotionEvents.promotionalDates.startDate.Year",
              month: "$promotionEvents.promotionalDates.startDate.Month",
              day: "$promotionEvents.promotionalDates.startDate.Day"
            }
          },
          endDate: {
            $dateFromParts: {
              year: "$promotionEvents.promotionalDates.endDate.Year",
              month: "$promotionEvents.promotionalDates.endDate.Month",
              day: "$promotionEvents.promotionalDates.endDate.Day"
            }
          },
          unit: "day"
        }
      }
    }
  }
])

该查询返回 durationInDays 以及指定 stores 文档的其他字段。

{
  "_id": "e6410bb3-843d-4fa6-8c70-7472925f6d0a",
  "eventName": "Massive Markdown Mania",
  "startDate": "2024-09-21T00:00:00.000Z",
  "endDate": "2024-09-29T00:00:00.000Z",
  "durationInDays": 8
}

相关内容