在两到两个文档之间获得时间差

Question

我正在做一个小型的物联网项目，我想展示一些我从设备中获得的有用信息。

设备是一个控制一个灯泡的开关

{
    "_id" : ObjectId("62baf5b98218da3bc80e9d61"),
    "light_on" : false,
    "device_id" : "ab082e4d-e617-4c74-8d41-fa316246afac",
    "timestamp" : ISODate("2022-07-01T10:00:24.000+0000")
}

{
    "_id" : ObjectId("62baf5b98218da3bc90e9d6a"),
    "light_on" : true,
    "device_id" : "ab082e4d-e617-4c74-8d41-fa316246afac",
    "timestamp" : ISODate("2022-07-01T10:32:00.000+0000")
}

按钮

1. 该 下，
2. 在最长的灯光
3. 的灯光持续时间处
4. 超过4个小时的闪电时，灯光在最长

所有持续时间超过了这种请求 不是即使确定它是可行的，因此欢迎任何帮助。

Answer 1

尝试这个：

db.collection.aggregate([
  {
    $setWindowFields: {
      partitionBy: "$device_id",
      sortBy: { timestamp: 1 },
      output: {
        following: {
          $shift: {
            output: "$timestamp",
            by: 1
          }
        }
      }
    }
  },
  {
    $set: {
      duration: {
        $dateDiff: {
          startDate: "$timestamp",
          endDate: "$following",
          unit: "second"
        }
      }
    }
  },
  {
    $facet: {
      mean: [
        {
          $group: {
            _id: {
              device_id: "$device_id",
              light_on: "$light_on"
            },
            mean: { $avg: "$duration" },
            longest: { $max: "$duration" }
          }
        }
      ],
      exceeded: [
        {
          $match: {
            light_on: true,
            duration: { $gt: 14400 } // 4 hours = 14400 seconds
          }
        }
      ]
    }
  }
])

mongo playground

for Mongodb版本＆lt; 5.0您需要其他解决方案。作为起点是这个：

db.collection.aggregate([
  { $sort: { timestamp: -1 } },
  {
    $group: {
      _id: "$device_id",
      timestamp: { $push: "$ROOT" }
    }
  },
  {
    $set: {
      timestamp: {
        $reduce: {
          input: "$timestamp",
          initialValue: [],
          in: {
            $concatArrays: [
              "$value",
              [
                {
                  $mergeObjects: [
                    "$this",
                    { following: { $last: "$value.timestamp" },
                      duration: {
                        $dateDiff: {
                          endDate: { $last: "$value.timestamp" },
                          startDate: "$this.timestamp",
                          unit: "second"
                        }
                      }
                    }
                  ]
                }
              ]
            ]
          }
        }
      }
    }
  },
  ... some cosmetic, see above
])