Get Time duration of two dates in MongoDB

Question

I'm trying to execute query in MongoDB Time duration between two dates Lets say

** startTime: ISODate('2019-09-17 20:15:00.000'), endTime: ISODate('2019-09-17 20:55:00.000') **

I want lastSeen-firstSeen as a duration in the output

Below is the query IM executing


db.TN_Collection.aggregate([
        {$match: {DE:{$in:devices},'T':{$gt: ISODate('2019-09-15 21:45:00.000'), $lt: ISODate('2019-09-15 22:25:00.000')}}},
        {$group: {_id: {A: '$A', DE: '$DE'}, firstSeen:{$min:'$T'}, lastSeen: {$max: '$T'}}}
])
.forEach(function(msg) {print(msg._id.A +',' + msg._id.DE+',' + msg.firstSeen+',' + msg.lastSeen)})

what do you mean by time duration ? Can you provide sample docs & expected o/p. & what was the issue with the existing query ? — whoami - fakeFaceTrueSoul, Oct 04 '19 at 15:03
Possible duplicate of [How to subtract two date time in mongodb](https://stackoverflow.com/questions/48369419/how-to-subtract-two-date-time-in-mongodb) — Himanshu Sharma, Oct 04 '19 at 17:02
@Mr.S.Sharma I've start time and end time. I've used $min and $max function to get first and last seen of 'A' against DE' present in my documents. after getting first seen and last seen I want total duration of A i.e lastseen-firstseen `eg. firstSeen: ISODate('2019-09-15 21:45:00.000')` `lastSeen: ISODate('2019-09-15 22:25:00.000')` I want ouput **lastSeen-firstSeen** ie. 40 minutes. I hope this will help @srinivasy — rajat singh, Oct 07 '19 at 04:50
@Mr.S.Sharma Using above query I'm getting outputs as `A: 1234, DE: 921, FirstSeen: ISODate('2019-09-15 21:45:00.000'), lastSeen: ISODate('2019-09-15 22:25:00.000')` Instead of firstSeen and lastSeen in my output I simply want subtract of these two dates ie **Duration**` — rajat singh, Oct 07 '19 at 05:21

Himanshu Sharma · Answer 1 · 2019-10-07T12:34:50.147

0

The following query can get us the expected output:

db.TN_Collection.aggregate([
    {
        $match:{
            "DE":{
                $in:["76612"]
            },
            "T":{
                $gte:ISODate('2019-09-11 07:20:55.298'),
                $lte:ISODate('2019-09-15 22:25:00.000')
            }
        }   
    },
    {
        $group:{
            "_id":{
                "A":"$A",
                "DE":"$DE"
            },
            "firstSeen":{
                $min:"$T"
            },
            "lastSeen":{
                $max:"$T"
            }
        }
    },
    {
        $project:{
            "_id":0,
            "A":"$_id.A",
            "DE":"$_id.DE",
            "Duration":{
                $divide:[
                    {
                        $subtract:[
                            "$lastSeen",
                            "$firstSeen"
                        ]
                    },
                    60000
                ]
            }
        }
    }
]).pretty()

Data set:

{
    "_id" : ObjectId("5d9b306b711a1e3a8167c712"),
    "DE" : "76612",
    "A" : "00101219",
    "T" : ISODate("2019-09-11T07:20:55.298Z"),
    "R" : -58
}
{
    "_id" : ObjectId("5d9b306b711a1e3a8167c713"),
    "DE" : "76612",
    "A" : "00101208",
    "T" : ISODate("2019-09-11T07:20:55.298Z"),
    "R" : -64
}
{
    "_id" : ObjectId("5d9b306b711a1e3a8167c714"),
    "DE" : "76612",
    "A" : "00100005",
    "T" : ISODate("2019-09-11T07:20:55.298Z"),
    "R" : -70
}
{
    "_id" : ObjectId("5d9b306b711a1e3a8167c715"),
    "DE" : "76612",
    "A" : "00101219",
    "T" : ISODate("2019-09-12T07:20:55.298Z"),
    "R" : -58
}
{
    "_id" : ObjectId("5d9b306b711a1e3a8167c716"),
    "DE" : "76612",
    "A" : "00101208",
    "T" : ISODate("2019-09-13T07:20:55.298Z"),
    "R" : -64
}
{
    "_id" : ObjectId("5d9b306b711a1e3a8167c717"),
    "DE" : "76612",
    "A" : "00100005",
    "T" : ISODate("2019-09-14T07:20:55.298Z"),
    "R" : -70
}

Output:

{ "A" : "00100005", "DE" : "76612", "Duration" : 4320 }
{ "A" : "00101208", "DE" : "76612", "Duration" : 2880 }
{ "A" : "00101219", "DE" : "76612", "Duration" : 1440 }

edited Oct 07 '19 at 12:34

answered Oct 07 '19 at 07:21

Himanshu Sharma

2,940
1
7
18

Thankyou for sharing this. please check this error after executing query `2019-10-07T07:41:47.325+0000 E QUERY [thread1] SyntaxError: unterminated string literal @stack.js:7:12 failed to load: stack.js` – rajat singh Oct 07 '19 at 07:37
`assert: command failed: { "ok" : 0, "errmsg" : "invalid operator '$toString'", "code" : 15999 } : aggregate failed _getErrorWithCode@src/mongo/shell/utils.js:25:13 doassert@src/mongo/shell/assert.js:13:14 assert.commandWorked@src/mongo/shell/assert.js:287:5 DBCollection.prototype.aggregate@src/mongo/shell/collection.js:1312:5 @ss.js:2:1` – rajat singh Oct 07 '19 at 09:48
which mongo version are you using? – Himanshu Sharma Oct 07 '19 at 09:53
MongoDB shell version: 3.2.9 – rajat singh Oct 07 '19 at 10:05
Is it fine if response comes like this: `{ "A" : "a", "DE" : "phone", "Duration" : 40 }` instead of `{ "A" : "a", "DE" : "phone", "Duration" : "40 minutes" }`? – Himanshu Sharma Oct 07 '19 at 10:32
Sure . Thanks for your help – rajat singh Oct 07 '19 at 10:42
@rajatsingh Updated the query accordingly. – Himanshu Sharma Oct 07 '19 at 11:16
Can you please post sample data on which you are testing this query? – Himanshu Sharma Oct 07 '19 at 11:28
`00502251,75579,Sun Sep 15 2019 22:24:52 GMT+0000 (UTC),Sun Sep 15 2019 22:24:57 GMT+0000 (UTC)` – rajat singh Oct 07 '19 at 11:33
This is the output I'm getting after executing the query that Ive mention in my question box Instead of firstSeen and lastSen I want total duration. i.e lastSeen - firstSeen – rajat singh Oct 07 '19 at 11:34
`{ "_id" : ObjectId("5d78a4b99eb772135951f376"), "DE" : "76612", "A" : "00101219", "T" : ISODate("2019-09-11T07:20:55.298Z"), "R" : -58 } { "_id" : ObjectId("5d78a4b99eb772135951f377"), "DE" : "76612", "A" : "00101208", "T" : ISODate("2019-09-11T07:20:55.298Z"), "R" : -64 } { "_id" : ObjectId("5d78a4b99eb772135951f379"), "DE" : "76612", "A" : "00100005", "T" : ISODate("2019-09-11T07:20:55.298Z"), "R" : -70 }` – rajat singh Oct 07 '19 at 11:37
this is the sample data. Ignore date on this particular sample data . these dates already have in my mongo db for which I'm looking for duration – rajat singh Oct 07 '19 at 11:40
@rajatsingh The query seems fine. I have added sample data and output... Please cross-check the range you are specifying in the query. – Himanshu Sharma Oct 07 '19 at 12:35
I dont know why still its not working in my case. As I've used correct Time Stamp. – rajat singh Oct 07 '19 at 13:03
Anyways thanks for helping me out on this. I'll investigate further – rajat singh Oct 07 '19 at 13:10
@rajatsingh Also let me know when you find the cause :) – Himanshu Sharma Oct 07 '19 at 15:59
Can we get duration without using $project ? – rajat singh Oct 08 '19 at 07:11
Can we discuss problem over a call because I believe there is some communication problem for explaining the situation. – rajat singh Oct 08 '19 at 07:14

Get Time duration of two dates in MongoDB

1 Answers1