查找用户在 mongoDB 中花费的总时间 [英] Find the total time spent by an user in mongoDB

问题描述

我有一个结构如下的mongodb:

|用户 |位置 |TimeOfVisit|U1.|自助餐厅.|ISODate("2018-12-27T09:09:08.688Z")|U2.|接待处.|ISODate("2018-12-27T09:12:45.688Z")|U1.|自助餐厅.|ISODate("2018-12-27T09:45:38.688Z")|U1.|自助餐厅.|ISODate("2018-12-27T09:47:38.688Z")

我需要找到用户在任何特定位置花费的总时间.我已经阅读了多个博客，但还没有找到任何具体的答案.我有以下几点:

aggregate([{$匹配":{用户":{$eq":req.query.User}}},{$ 组":{"_id": "$location",总分钟数":{<<获取时间>>}}}

解决方案

您可以从 TimeOfVisit 字段中找到 timeOfDay，然后使用 $sum 得到总数计数

db.collection.aggregate([{ "$match": { "User": req.query.User }},{$addFields":{时间":{"$mod": [{ "$toLong": "$TimeOfVisit" },1000*60*60*24]}}},{$组":{"_id": "$location","totalTimeInMilliseconds": { "$sum": "$timeOfDay" }}}])

输出

<预><代码>[{"_id": "接待处",totalTimeInMilliseconds":33165688},{"_id": "自助餐厅",totalTimeInMilliseconds":98846064}]

您可以进一步划分它以获得天数、小时数、分钟数或秒数

1 小时 = 60 分钟 = 60 × 60 秒 = 3600 秒 = 3600 × 1000 毫秒 = 3,600,000 毫秒.

db.collection.aggregate([{$addFields":{时间":{"$mod": [{ "$subtract": [ "$TimeOfVisit", Date(0) ] },1000*60*60*24]}}},{$组":{"_id": "$location",totalTimeInMiniutes":{"$sum": { "$divide": ["$timeOfDay", 60 × 1000] }}}}])

对于 mongodb 4.0 及以上

db.collection.aggregate([{$addFields":{时间":{"$mod": [{ "$toLong": "$TimeOfVisit" },1000*60*60*24]}}},{$组":{"_id": "$location",totalTimeInMiniutes":{"$sum": { "$divide": ["$timeOfDay", 60 × 1000] }}}}])

I have a mongodb with the structure as below:

|User |Location  |TimeOfVisit
|U1.  |Cafeteria.|ISODate("2018-12-27T09:09:08.688Z")
|U2.  |Reception.|ISODate("2018-12-27T09:12:45.688Z")
|U1.  |Cafeteria.|ISODate("2018-12-27T09:45:38.688Z")
|U1.  |Cafeteria.|ISODate("2018-12-27T09:47:38.688Z")

I need to find the total amount of time taken by an user in any particular location. I have read across multiple blogs and yet to find any concrete answer. I have the following:

aggregate([
        {
            "$match": {
                "User": {
                    "$eq": req.query.User
                }
            }
        },
        {
            "$group": {
                "_id": "$location",
                "totalMiniutes": {
                    <<Get the time>>
                }
            }
        }

解决方案

You can find the timeOfDay from the TimeOfVisit field and then use $sum to get the total count

db.collection.aggregate([
  { "$match": { "User": req.query.User }},
  { "$addFields": {
    "timeOfDay": {
      "$mod": [
        { "$toLong": "$TimeOfVisit" },
        1000*60*60*24
      ]
    }
  }},
  { "$group": {
    "_id": "$location",
    "totalTimeInMilliseconds": { "$sum": "$timeOfDay" }
  }}
])

Output

[
  {
    "_id": "Reception",
    "totalTimeInMilliseconds": 33165688
  },
  {
    "_id": "Cafeteria",
    "totalTimeInMilliseconds": 98846064
  }
]

You can further divide it to get the days, hours, minutes or seconds

1 hour = 60 minutes = 60 × 60 seconds = 3600 seconds = 3600 × 1000 milliseconds = 3,600,000 ms.

db.collection.aggregate([
  { "$addFields": {
    "timeOfDay": {
      "$mod": [
        { "$subtract": [ "$TimeOfVisit", Date(0) ] },
        1000 * 60 * 60 * 24
      ]
    }
  }},
  { "$group": {
    "_id": "$location",
    "totalTimeInMiniutes": {
      "$sum": { "$divide": ["$timeOfDay", 60 × 1000] }
    }
  }}
])

For the mongodb 4.0 and above

db.collection.aggregate([
  { "$addFields": {
    "timeOfDay": {
      "$mod": [
        { "$toLong": "$TimeOfVisit" },
        1000 * 60 * 60 * 24
      ]
    }
  }},
  { "$group": {
    "_id": "$location",
    "totalTimeInMiniutes": {
      "$sum": { "$divide": ["$timeOfDay", 60 × 1000] }
    }
  }}
])

这篇关于查找用户在 mongoDB 中花费的总时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！