如何在mongodb中找到两个日期之间的时差 [英] How to find the hours difference between two dates in mongodb

问题描述

我将以下结构的文档保存在mongodb中.

I have the documents in following structure saved in my mongodb.

UserLogs

UserLogs

{
"_id":"111",
"studentID" : "1",
"loginTime" : "2019-05-01 09:40:00",
"logoutTime" : "2019-05-01 19:40:00"
},
{
"_id":"222",
"studentID" : "1",
"loginTime" : "2019-05-02 09:40:00",
"logoutTime" : "2019-05-02 20:40:00"
},
{
"_id":"333",
"studentID" : "2",
"loginTime" : "2019-05-02 09:40:00",
"logoutTime" : "2019-05-02 20:40:00"
}

是否可以查询登录时间和注销时间之间的时间段的文档.例如:磨碎时间超过20小时

is it possible to query for documents where the period of time between loginTime and logoutTime.eg: grater than 20 hrs

mongodb version = 3.4

推荐答案

您可以在下面的聚合中使用

You can use below aggregation

db.collection.aggregate([
  { "$project": {
    "difference": {
      "$divide": [
        { "$subtract": ["$logoutTime", "$loginTime"] },
        60 * 1000 * 60
      ]
    }
  }},
  { "$group": {
    "_id": "$studentID",
    "totalDifference": { "$sum": "$difference" }
  }},
  { "$match": { "totalDifference": { "$gte": 20 }}}
])

您只需 $subtract loginTime和 $divide 与3600000一起获取以小时为单位的时间.

You have to just $subtract loginTime from logoutTime and it will give you the subtracted timestamp and then just $divide it with 3600000 to get the time in hours.

MongoPlayground

这篇关于如何在mongodb中找到两个日期之间的时差的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！