需求描述:遇到mongodb 求记录中 计算两时间(ISODate)差值并求和,故对比mysql 里面的TIMESTAMPDIFF做个记录(ps:写此篇,主要原因是在百度3min中内没解决)
mysql :demo如图
SELECT START_TIME,END_TIME FROM alarm_info WHERE ALARM_SEQ=292 OR ALARM_SEQ=291
UNION ALL
SELECT "sum开始结束时间(s)",SUM(TIMESTAMPDIFF(SECOND,START_TIME,END_TIME)) coun FROM alarm_info WHERE ALARM_SEQ=292 OR ALARM_SEQ=291
2: mongodb 代码如下
#聚合查询单根桩在线总时长,上下线次数
db.getCollection('LogAndOutMerge2').aggregate([
{ $match : {stakeId:NumberLong("6410")}}
,{$project: {_id:'$stakeId', duration : {$subtract : ['$endTime', '$startTime']}}}
,{ $group: { _id : "$stakeId", onlineTime: { $sum:{ $multiply: ["$duration"] } },loginOutCnt: { $sum: 1 }} }
])
mongod 通过聚合,用于过滤数据,将集合中的文档分组的方式,进行将多条记录时间差并求和
ps:参考https://stackoverflow.com/questions/41138877/how-to-calculate-timestamp-difference-in-mongodb-in-hours
官方文档:https://docs.mongodb.com/manual/reference/operator/aggregation/subtract/