获取两个日期之间的总在线时间

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=login，false=logout)之间的总在线时间我想要接收的文档看起来像这样：

{
  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; }

​