CODEWITHSUNDEEP
mongodbmongodb-querypymongo
Yatin Pandit
Yatin Pandit

Reputation: 57

Average of time interval in mongo

I am trying to make a system where i have to make an average of data of 2 mins and display it to the user. I tried groupby aggregate functions. But maybe I did not write the query properly.

 [
  {
    deviceid: 1,
    bucketSize: 4,
    first: 1573833152,
    last: 1573833155,
    samples: [
      {
        val: 10,
        time: 1573833152
      },
      {
        val: 15,
        time: 1573833153
      },
      {
        val: 14,
        time: 1573833154
      },
      {
        val: 16,
        time: 1573833165
      },
      {
        val: 22,
        time: 1573833166
      },
      {
        val: 26,
        time: 1573833167
      },
      {
        val: 27,
        time: 1573833168
      },
      {
        val: 28,
        time: 1573833169
      },
      {
        val: 29,
        time: 1573833170
      }
    ]
  },
  {
    deviceid: 1,
    bucketSize: 4,
    first: 1573833156,
    last: 1573833160,
    samples: [
      {
        val: 30,
        time: 1573833156
      },
      {
        val: 45,
        time: 1573833157
      },
      {
        val: 54,
        time: 1573833158
      },
      {
        val: 60,
        time: 1573833159
      },
      {
        val: 70,
        time: 1573833160
      }
    ]
  },
  {
    deviceid: 1,
    bucketSize: 4,
    first: 1573833161,
    last: 1573833165,
    samples: [
      {
        val: 10,
        time: 1573833161
      },
      {
        val: 15,
        time: 1573833162
      },
      {
        val: 14,
        time: 1573833163
      },
      {
        val: 20,
        time: 1573833164
      }
    ]
  }
]

This is the data. I need an average of every 2 minutes. I am new to Mongo. Hence stuck with this problem. Thank you really very much in advance.

Playground for average. Which one of the stackoverflow member helped me to create. https://mongoplayground.net/p/tuTzNeO2DKx

Upvotes: 3

Views: 1027

Answers (1)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59557

For me it is not fully clear what you mean by "I need an average of every 2 minutes". All your sample data fall into one 2-Minute interval.

Anyway, the key operation is to determine the 2-Minutes interval. Afterwards you can $group by samples.interval value. You can do it like this one:

db.collection.aggregate([
   { $unwind: "$samples" },
   {
      // convert time value into proper 'Date' object and split into parts
      $addFields: {
         "samples.interval": {
            $dateToParts: {
               date: {
                  $toDate: {
                     $multiply: [1000, "$samples.time"]
                  }
               }
            }
         }
      }
   },
   {
      // Build new 2-Minute interval. `samples.interval` represents the start time of 2-Minute interval
      $addFields: {
         "samples.interval": {
            $dateFromParts: {
               year: "$samples.interval.year",
               month: "$samples.interval.month",
               day: "$samples.interval.day",
               hour: "$samples.interval.hour",
               minute: { $subtract: ["$samples.interval.minute", { $mod: ["$samples.interval.minute", 2] }] }
            }
         }
      }
   },
   {
      $group: {
         _id: {
            deviceid: "$deviceid",
            interval: "$samples.interval"
         },
         average: { $avg: "$samples.val" }
      }
   },
   { $replaceRoot: { newRoot: { $mergeObjects: ["$$ROOT", "$_id"] } } },
   { $unset: "_id" }
])

You may use 

minute: { $add: [1, { $subtract: ["$samples.interval.minute", { $mod: ["$samples.interval.minute", 2] }] }] }

in order to get the interval as interval: ISODate("2019-11-15T17:27:00.000+0100") ± 1 Minute rather than "start of interval"

Upvotes: 2

Related Questions