CODEWITHSUNDEEP
javascriptnode.jsmongodbmongoose
danibrum
danibrum

Reputation: 529

Average value of a whole collection on mongoDB

I need to get the average value of a whole mongo collection. To be more specific, I have two date fields, let's call them beginning and end.

First of all, I need to do something like end - beginning time to get the elapsed time. After that, I want to sum all the elapsed time and get the average time.

I need this data to plot a chart. I've seen that mongoDB has some built in functions like subtract, sum and average as well. I don't know how to use them, and I also need the data in minutes so I may use some Javascript to convert it, I don't know yet but this is not the problem.

Mongoose schema:

module.exports = mongoose.model('atendimento', {
    id: String,
    id_atendimento: { type: Number, default: 0 },
    id_cliente: { type: Number, default: 0 },
    id_user: mongoose.Schema.Types.ObjectId,
    user_nome: String,
    cliente_nome: String,
    id_atendente: { type: Number, default: 0 },
    atendente_nome: String,
    atendente_imagem: String,
    setor: Number,
    descricao: String,
    status: String,
    date: { type: Date, default: Date.now },
    inicio: { type: Date, default: Date.now },
    fim: { type: Date, default: Date.now },
    update: { type: Date, default: Date.now }
});

The begin is the variable called inicio and the end is the variable called fim.

At the moment I need help with these mongo functions or any other suggestions will be welcome.

Thanks in advance, hope I can get some help!

Upvotes: 0

Views: 1644

Answers (2)

chridam
chridam

Reputation: 103355

Sounds like you need to run an aggregation pipeline which aggregates the whole collection using a $group pipeline.

Within the $group, you need to calculate the timestamp difference with $subtract operator and divide the result by the number of milliseconds in a minute (60 * 1000) with $divide operator.

You will then apply the $avg operator to the above expression so that you will have your collection average.

For the $group pipeline, you can specify an _id value of null to calculate accumulated values for all the input documents as a whole.

Following example shows the above:

Atendimento.aggregate([
    { '$group': {
        '_id': null,
        'average_duration': {
            '$avg': {
                '$divide': [
                    { '$subtract': ['$fim', '$inicio'] }, 
                    60*1000
                ]
            }
        }
    } }

]).exec((err, results) => console.log(results))

Upvotes: 1

Ridham Tarpara
Ridham Tarpara

Reputation: 6160

You need to user aggregate function with $subtract function.Subtracts two dates to return the difference in milliseconds

db.sales.aggregate( [ { $project: { item: 1, dateDifference: { $subtract: [ new Date(), "$date" ] } } } ] )

this will give you data like

{ "_id" : 1, "item" : "abc", "dateDifference" : NumberLong("11713985194") }
{ "_id" : 2, "item" : "jkl", "dateDifference" : NumberLong("11710385194") }

You can change the new date to another date field you have in the database.

Then you need to use $substract and $group in aggregate function to get the desired result as you haven't posted exact schema so it's hard to write your query so you can try with following

https://docs.mongodb.com/manual/reference/method/db.collection.aggregate/ https://docs.mongodb.com/manual/reference/operator/aggregation/subtract/ https://docs.mongodb.com/manual/reference/operator/aggregation/group/

Upvotes: 1

Related Questions