How to get average of one field value per min from mongo with aggregate query?

Question

I saved my data in MongoDb as

{
  "_id": "ObjectId(\"5fec31b6b9022035abbbf7cc\")",
  "message": {
    "date": "2020-12-30 13:20:26",
    "time": "2020-12-30T07:50:26.000Z",
    "ID": "005",
    "P": 1.36
  }
},
{
  "_id": "ObjectId(\"5fec31b5b9022035abbbf7c2\")",
  "message": {
    "date": "2020-12-30 13:20:24",
    "time": "2020-12-30T07:50:24.000Z",
    "ID": "005",
    "P": 1.5
  }
 
},
{
  "_id": "ObjectId(\"5fec31b0b9022035abbbf7b3\")",
  "message": {
    "date": "2020-12-30 13:20:19",
    "time": "2020-12-30T07:50:19.000Z",
    "ID": "005",
    "P": 1.63
  }
  
}

I want to find the average of P value per min. I've tried Group result by 15 minutes time interval in MongoDb but I got an error in the time field.

db.pressure.aggregate([
  { "$group": {
    "_id": {
      "year": { "$year": "message.time" },
      "dayOfYear": { "$dayOfYear": "message.time" },
      "hour": { "$hour": "message.time" },
      "interval": {
        "$subtract": [ 
          { "$minute": "message.time" },
          { "$mod": [{ "$minute": "message.time"}, 1] }
        ]
      }
    },
      
  
    "count": { "$sum": 1 }
  }
  }
  
])

{ "message" : "can't convert from BSON type string to Date", "ok" : 0, "code" : 16006, "codeName" : "Location16006", "name" : "MongoError" }

Storing date/time values as **string** is a design flaw. Use always proper `Date` object - then your aggregation pipeline will work. — Wernfried Domscheit, Dec 30 '20 at 13:51

turivishal · Accepted Answer · 2020-12-30T13:35:04.667

2

Few Fixes:

You need to convert your string date to date type using $toDate (From MongoDB 4.0), $addFields will update message.time field to date type
$year, $hour etc operators requires reference of field using $ sign you missed it in message.time
use $avg to get average of message.P

db.collection.aggregate([
  { $addFields: { "message.time": { $toDate: "$message.time" } } },
  {
    "$group": {
      "_id": {
        "year": { "$year": "$message.time" },
        "dayOfYear": { "$dayOfYear": "$message.time" },
        "hour": { "$hour": "$message.time" },
        "interval": {
          "$subtract": [
            { "$minute": "$message.time" },
            { "$mod": [{ "$minute": "$message.time" }, 15] }
          ]
        }
      },
      "count": { "$count": 1 },
      "average": { "$avg": "$message.P" }
    }
  }
])

Playground

MongoDB 3.6: you can use $dateFromString

  {
    $addFields: {
      "message.time": {
        $dateFromString: { dateString: "$message.time" }
      }
    }
  }

Playground

edited Dec 30 '20 at 13:35

answered Dec 30 '20 at 12:40

turivishal

34,368
7
36
59

"time": "2020-12-30T07:50:24.000Z", is this type string .but in mongo it showing as date type – AkhilaV Dec 30 '20 at 12:45
{ "message" : "Unrecognized expression '$toDate'", "ok" : 0, "code" : 168, "codeName" : "InvalidPipelineOperator", "name" : "MongoError" } – AkhilaV Dec 30 '20 at 12:46
what is the version of your mongodb? – turivishal Dec 30 '20 at 12:47
i think its 3.6 – AkhilaV Dec 30 '20 at 12:50
`$toDate` is from mongodb v4.0 i have added another option for 3.6 it is `$dateFromString` – turivishal Dec 30 '20 at 12:53
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/226603/discussion-between-akhilav-and-turivishal). – AkhilaV Dec 30 '20 at 13:10

How to get average of one field value per min from mongo with aggregate query?

1 Answers1