Mongodb, group by datediff and getting hour

Question

I m actually developping an application and I need to get some informations from my database :

• I have a traitement model in which I got a user,
• The traitement model has start date and an end date, both in Date format, so mongodb can use ISODate()

What I need is to get all the "traitements" by user for a datedifference, but in hours format. For example, if the traitement started the 24/02/2015 08:00:00 and ended the 24/02/2015 at 10:00:00, I need to get "2" hours.

Here's what I have actually :

@TraitementNettoyage.aggregate([
        {$group: {'_id': {user: '$user'}, time: {'$subtract': ['$dateSortie', '$dateEntre']}}},
        {$sort: {_id: 1}}
      ]).exec((err, res)=>
        console.log res
      )

This request doesn't work and tells me that $subtract is an unknown group operator.

EDIT : I can use $subtract but I dont know how to group on it. Can you help me ?

Here's my new code without group :

 Traitement.aggregate([
        {$project: {total: {$subtract: ['$dateSortie', '$dateEntre']}}},
        {$sort: {_id: 1}}
      ]).exec((err, res)=>
        console.log res
      )

The last part is to group it by user attribute and I dont know how to make it work :-/

EDIT 2 :

A document of what I need:

[{
    "_id": {
        "user": {
            "_id": "5512a66db54f879c2887411f",
            "userLastname": "Toto",
            "userFirstname": "Toto"
        },
        "total": 17824
    }
},
{
    "_id": {
        "user": {
            "_id": "551408741ad3b66c19978e9a",
            "userLastname": "Foo",
            "userFirstname": "Foo"
        },
        "total": 939
    }
}]

And heres a simple "traitement" document :

    {"_id" : ObjectId("55153711eba735e4311f92a0"),
                    "dateEntre" : ISODate("2015-03-27T10:55:13.069Z"),
                    "dateSortie" : ISODate("2015-03-27T10:55:18.018Z"),
                    "user" : ObjectId("5512a66db54f879c2887411f"),
                    "__v" : 0
}

What I really need is :

SUM of all the (dateSortie-dateEntre) grouped by user .

THanks for advance

Answer 1

From what I understood in your question (you actually need to provide a sample couple of documents with your schema) if your Traitement model has the following structure, for instance:

/* 0 */
{
    "_id" : 1,
    "user" : "abc",
    "dateEntre" : ISODate("2014-03-01T08:00:00.000Z"),
    "dateSortie" : ISODate("2014-03-01T13:00:00.000Z")
}

/* 1 */
{
    "_id" : 2,
    "user" : "jkl",
    "dateEntre" : ISODate("2014-03-01T08:00:00.000Z"),
    "dateSortie" : ISODate("2014-03-01T10:30:00.000Z")
}
/* 2 */
{
    "_id" : 3,
    "user" : "jkl",
    "dateEntre" : ISODate("2014-03-01T12:00:00.000Z"),
    "dateSortie" : ISODate("2014-03-01T18:00:00.000Z")
}

Your aggregation framework would have a single $project pipeline operation where you get the difference between the two dates by using the $subtract operator and then transform that date difference in milliseconds to hours by using the $divide operator. The last stage in your pipeline will be to use the $group operator to group the documents from the previous pipeline and $sum the hours in date difference:

Traitement.aggregate([ 
    { 
        $project: { 
            user: 1,             
            dateDifference: { 
                $divide: [{ 
                    $subtract: [ "$dateSortie", "$dateEntre" ]
                    }, 1000*60*60
                ] 
            }
         }
    },
    { 
        $group: { 
            _id: "$user",             
            total : { 
                $sum : "$dateDifference"
            }
        }
    } 
])

Results:

/* 0 */
{
    "result" : [ 
        {
            "_id" : "jkl",
            "total" : 8.5
        }, 
        {
            "_id" : "abc",
            "total" : 5
        }
    ],
    "ok" : 1
}