Menu
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.
755
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 .
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 .
$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.
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.
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.
93
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
