Using MongoDB to group by the difference in dates and retrieve the hour value

Question

Using MongoDB to group by the difference in dates and retrieve the hour value

Currently, I am working on an application and I require some information from my database:

I have a model called "traitement" which includes a user,
The "traitement" model has a start date and an end date, both in Date format, allowing MongoDB to use ISODate()

What I am looking to achieve is to retrieve all the "traitements" by user for a specific date range, but in hours format. For instance, if a "traitement" started on 24/02/2015 08:00:00 and ended on 24/02/2015 at 10:00:00, I need to calculate and get "2" hours.

Below is my current code snippet:

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

This query is not functioning as it is indicating that $subtract is an unknown group operator.

EDIT : Although I can use $subtract, I am unsure how to group based on it. Can someone provide guidance?

Here's the revised code without grouping:

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

The final step is to group it by the user attribute and I am struggling to make it work :-/

EDIT 2 :

Here is a sample document of the desired output:

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

And here is 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
}

The ultimate goal is to calculate the SUM of all the durations (dateSortie-dateEntre) grouped by user.

Thank you in advance

javascript mongodb coffeescript mongoose

Answer 1

Answer №1

After reviewing your inquiry (please provide a sample of documents with your schema), it appears that if your Traitement model has the structure outlined below:

/* 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 should include a $project pipeline operation where you calculate the time difference between two dates using the $subtract operator, and then convert that difference into hours using the $divide operator. The final stage in the pipeline should involve the $group operator to group the documents and calculate the total hours:

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
}

Answer 2

After reviewing your inquiry (please provide a sample of documents with your schema), it appears that if your Traitement model has the structure outlined below:

/* 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 should include a $project pipeline operation where you calculate the time difference between two dates using the $subtract operator, and then convert that difference into hours using the $divide operator. The final stage in the pipeline should involve the $group operator to group the documents and calculate the total hours:

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
}

Using MongoDB to group by the difference in dates and retrieve the hour value

Answer №1

Similar questions

Setting up a new user in a mongoDB database using NodeJS

What is the best way to retrieve information from an http website and display it in an html table using javascript

How can you retrieve the index of the outer repeater item within nested ng-repeaters in AngularJS?

Assign a unique ID to each Angular Material checkbox

Identifying if a variable is redirecting

Disabling hover effects in Chart.js: A step-by-step guide

Using JavaScript to display a line using `document.write`

Having difficulties importing Container from NextUi

Why doesn't express.js throw an error when the variable 'app' is used within its own definition?

ngRepeat does not completely refresh the DOM when dealing with a basic array

Initiating rs.initiate() in MongoDB leads to high demands on disk space

Incorporating an AngularJs App into Joomla: A Step-by-

Looking for a specific search term within the middle of strings in an array

The issue of 'MessageChannel not defined' arises specifically on web pages that have implemented reCaptcha v2

Choosing custom element children once they're connected to the DOM: A guide

What is the most efficient method for converting a string into an object in a Node.js environment?

AJAX forms and snippets

What could be causing my web application to not properly identify angular.js?

The functionality of Jquery ceases to work once a setTimeout function is implemented

What is the method for transforming an array object into an object?