Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to get sum of differences from two dates in mongodb

I want to sum of differences from starttime and endtime in minutes.

{
    "_id" : ObjectId("56cd544df7851e850d8b4573"),
    "user_id" : "281",
    "document_id" : "1455614372.pdf",
    "page_number" : "1",
    "starttime" : ISODate("48118-03-20T01:35:14Z"),
    "endtime" : ISODate("48118-03-20T04:29:10Z")
}
{
    "_id" : ObjectId("56cd544df7851e850d8b4574"),
    "user_id" : "281",
    "document_id" : "1455614372.pdf",
    "page_number" : "1",
    "starttime" : ISODate("48118-03-20T14:29:49Z"),
    "endtime" : ISODate("48118-06-22T12:52:36Z")
}
{
    "_id" : ObjectId("56cd544df7851e850d8b4575"),
    "user_id" : "281",
    "document_id" : "1455614372.pdf",
    "page_number" : "2",
    "starttime" : ISODate("48118-03-20T04:29:10Z"),
    "endtime" : ISODate("48118-03-20T14:29:49Z")
}

my collection name is pdftracker.

like image 755
Ravi Solanki Avatar asked Feb 25 '16 05:02

Ravi Solanki


People also ask

How do I find the difference between two dates in MongoDB?

The $dateDiff expression returns the integer difference between the startDate and endDate measured in the specified units . Durations are measured by counting the number of times a unit boundary is passed. For example, two dates that are 18 months apart would return 1 year difference instead of 1.5 years .

How sum is calculated in MongoDB?

If used on a field that contains both numeric and non-numeric values, $sum ignores the non-numeric values and returns the sum of the numeric values. If used on a field that does not exist in any document in the collection, $sum returns 0 for that field. If all operands are non-numeric, $sum returns 0 .

What is $EXPR in MongoDB?

$expr can build query expressions that compare fields from the same document in a $match stage. If the $match stage is part of a $lookup stage, $expr can compare fields using let variables. See Perform Multiple Joins and a Correlated Subquery with $lookup for an example.

What is $project in MongoDB?

Definition. $project. Passes along the documents with the requested fields to the next stage in the pipeline. The specified fields can be existing fields from the input documents or newly computed fields.


1 Answers

You don't say which fields you want to group on, but assuming you want a "user_id" and "document_id" combination. Whatever it's just the _id field in the following $group statement.

As for the "interval", then date math is on your side, and when you $subtract one Date object from another, then the result is the "difference" represented in "milliseconds". So that again just takes a little conversion:

db.collection.aggregate([
    { "$group": {
        "_id": { "user_id": "$user_id", "document_id": "$document_id" },
        "totalMinites": {
            "$sum": {
                "$divide": [
                    { "$subtract": [ "$endtime", "$starttime" ] },
                    1000 * 60
                ]
            }
        }
    }}
])

Simple math rolled into $sum for the total on the grouping. And 1000 milliseconds multiplied by 60 seconds as a divisor on a millisecond result does the conversion to minutes.

like image 93
Blakes Seven Avatar answered Nov 15 '22 03:11

Blakes Seven