获取两次约会之间的总在线时间

Question

我有以下集合：

[
{ "_id" : ObjectId("5e7b38534512e95591e14c59"), "name" : "Luna", "online" : true },
{ "_id" : ObjectId("5e7b386b4512e95591e14c5a"), "name" : "Luna", "online" : false },
{ "_id" : ObjectId("5e7b386b4512e95591e14c5b"), "name" : "Luna", "online" : true },
{ "_id" : ObjectId("5e7b4e128e6bf5694924db09"), "name" : "Luna", "online" : false },
{ "_id" : ObjectId("5e7b4ec81b36e86a1fb754bf"), "name" : "Luna", "online" : true },
{ "_id" : ObjectId("5e7b4f3b1b36e86a1fb754c0"), "name" : "Luna", "online" : false },
{ "_id" : ObjectId("5e7b4f581b36e86a1fb754c1"), "name" : "Luna", "online" : true },
{ "_id" : ObjectId("5e7b5065f5700f6bee612040"), "name" : "Luna", "online" : false },
{ "_id" : ObjectId("5e7b51a3f5700f6bee612041"), "name" : "Luna", "online" : true },
{ "_id" : ObjectId("5e7b5885f5700f6bee612042"), "name" : "Luna", "online" : false },
{ "_id" : ObjectId("5e7b588ff5700f6bee612043"), "name" : "Luna", "online" : true }
]

现在，我想要的查询是一个匹配两个日期之间的数据的查询，并计算这些日期之间的总在线时间。使用mongoId作为时间戳。

现在我已经找到了如何查询两个日期之间的数据：

var objectIdFromDate = function (date) {
if(typeof date === 'string') date = new Date(date);
return ObjectId( Math.floor(date.getTime() / 1000).toString(16) + "0000000000000000" );
};
db.getCollection('timeline').aggregate([
{$match:{
name:"Luna",
_id:{
$gte: objectIdFromDate("2020-03-25T10:54:35.000Z"),
$lt: objectIdFromDate("2020-03-25T12:36:53.000Z") 
}
}},
// ... ??? ...
])

我想不通的是，如何计算在线状态(true=登录，false=注销(之间的总在线时间我想收到的文件大概是这样的：

{
name:"Luna",
totalTimeOnline:<datetime here>
}

Answer 1

您可以按在线/离线对持续时间进行分组，并减少值以获得总计数。

显示的结果时间为HH:MM:SS，持续时间以毫秒为单位。

如果你注意到的话，我的时间间隔是基于斐波那契序列的，01:00和01:30除外，因为我需要使它们不同。

当当前登录为联机时，用户从那时起就一直处于脱机状态。

const main = () => {
let sessions = calculateSessions(logins);
Object.keys(sessions).forEach((key) => {
let duration = sessions[key].reduce((sum, ms) => sum + ms);
console.log(`Time spent ${key}: ${formatTime(duration)}`);
});
}
let logins = [
{ "id" :  1, "timestamp" : "2020-03-23T00:00:00.000Z", "online" : true  },
{ "id" :  2, "timestamp" : "2020-03-23T01:00:00.000Z", "online" : false },
{ "id" :  3, "timestamp" : "2020-03-23T01:30:00.000Z", "online" : true  },
{ "id" :  4, "timestamp" : "2020-03-23T02:00:00.000Z", "online" : false },
{ "id" :  5, "timestamp" : "2020-03-23T03:00:00.000Z", "online" : true  },
{ "id" :  6, "timestamp" : "2020-03-23T05:00:00.000Z", "online" : false },
{ "id" :  7, "timestamp" : "2020-03-23T08:00:00.000Z", "online" : true  },
{ "id" :  9, "timestamp" : "2020-03-23T13:00:00.000Z", "online" : false },
{ "id" : 10, "timestamp" : "2020-03-23T21:00:00.000Z", "online" : true  },
{ "id" : 12, "timestamp" : "2020-03-24T10:00:00.000Z", "online" : false },
{ "id" : 13, "timestamp" : "2020-03-25T07:00:00.000Z", "online" : true  }
];
// Assuming dates are pre-sorted and the online status is always staggered
const calculateSessions = (logins) => {
return logins.reduce((sessions, login, index, all) => {
if (index > 0) {
let duration = new Date(login.timestamp) - new Date(all[index - 1].timestamp);
sessions[login.online ? 'offline' : 'online'].push(duration);
}
return sessions;
}, { online : [], offline : [] });
};
const formatTime = (ms) => {
const pad = (n, z = 2) => ('00' + n).slice(-z);
const hh = pad(ms / 3.6e6 | 0);
const mm = pad((ms % 3.6e6) / 6e4 | 0);
const ss = pad((ms % 6e4) / 1000 | 0);
const mmm = pad(ms % 1000, 3);
return `${hh}:${mm}:${ss}.${mmm}`;
};
main();

.as-console-wrapper { top: 0; max-height: 100% !important; }

---

特定用例

const main = () => {
let details = calculateTimeOnline('Luna', logins);
console.log(Object.assign({}, details, {
totalTimeOnline : formatTime(details.totalTimeOnline)
}));
}
let logins = [
{ "id" :  1, "timestamp" : "2020-03-23T00:00:00.000Z", "online" : true  },
{ "id" :  2, "timestamp" : "2020-03-23T01:00:00.000Z", "online" : false },
{ "id" :  3, "timestamp" : "2020-03-23T01:30:00.000Z", "online" : true  },
{ "id" :  4, "timestamp" : "2020-03-23T02:00:00.000Z", "online" : false },
{ "id" :  5, "timestamp" : "2020-03-23T03:00:00.000Z", "online" : true  },
{ "id" :  6, "timestamp" : "2020-03-23T05:00:00.000Z", "online" : false },
{ "id" :  7, "timestamp" : "2020-03-23T08:00:00.000Z", "online" : true  },
{ "id" :  9, "timestamp" : "2020-03-23T13:00:00.000Z", "online" : false },
{ "id" : 10, "timestamp" : "2020-03-23T21:00:00.000Z", "online" : true  },
{ "id" : 12, "timestamp" : "2020-03-24T10:00:00.000Z", "online" : false },
{ "id" : 13, "timestamp" : "2020-03-25T07:00:00.000Z", "online" : true  }
];
// Assuming dates are pre-sorted and the online status is always staggered
const calculateTimeOnline = (user, logins) => {
return logins.reduce((result, login, index, all) => {
return index > 0 && !login.online
? Object.assign(result, {
totalTimeOnline : result.totalTimeOnline +
new Date(login.timestamp).getTime() -
new Date(all[index - 1].timestamp).getTime()
})
: result;
}, {
name: user,
totalTimeOnline: 0
});
};
const formatTime = (ms) => {
const pad = (n, z = 2) => ('00' + n).slice(-z);
const hh = pad(ms / 3.6e6 | 0);
const mm = pad((ms % 3.6e6) / 6e4 | 0);
const ss = pad((ms % 6e4) / 1000 | 0);
const mmm = pad(ms % 1000, 3);
return `${hh}:${mm}:${ss}.${mmm}`;
};
main();

.as-console-wrapper { top: 0; max-height: 100% !important; }