0

I'm looking for the best way to calculate the average of a couple of fields in my database over a set period of time.

I know that through this I can get last days data (as an example)

$where: function () { return Date.now() - this._id.getTimestamp() < (24 * 60 * 60 * 1000)  }}

And through aggregate I can project the average of fields

db.third_graph.aggregate([{$project: {  avgSum: { $avg: "$Sum"}, avgCars: { $avg: "$No of cars" }  } }])

Collection's items are like this one

{
_id: ObjectId("58da3f98d8291bf847112a19"),
date: "2017-03-28 12:48:55",
FleetID: "3",
"High value": 100,
"Min value": 31,
"No of cars": 310,
Mean: 71.12,
"Standard Deviation": 14.78,
Sum: 22048,
"Potentian km": 17638.4,
"Minutes*10000": 8.82,
Trips: 2939.73,
"Potential revenue *10000": 1.0584,
"Scales of charge": [
0,
0,
5,
24,
38,
66,
70,
76,
31
]
}

Since data are written circa every 15 minutes I'd like to do get the following:

  • average on the hour in the last 24 hours
  • average of the last 24 hours
  • average of the day for the last month/date range

I found something here on SO and on the web about using project and group but it casts errors

db.third_graph.aggregate([{ $match: { date: {$gte: ISODate("2017-08-01T00:00:00.0Z"), $lt: ISODate("2017-08-03T00:00:00.0Z")}}, {$group: {_id: "$FleetID", media: { $avg: "$Sum"}}])

Any help will be appreciated

EDIT: I rolled with this solution:

db.third_graph.aggregate([ {"$match" : { date:{"$gt" : "'`date --date="48 hours ago" -Iseconds`'","$lt" : "'`date -Iseconds`'"}}}, { "$group" : { "_id" : "$FleetID", "average" : { "$avg" : "$Sum" }, "Average Cars" : { "$avg" : "$No of cars" }}}, {$sort:{_id: 1}}])
Federico Cupellini
  • 23
  • 7
  • Because your dates are "strings". You need to convert those strings into BSON Date – Neil Lunn Aug 05 '17 at 23:18
  • I'm sorry, maybe I'm not clear. I can use also the timestamp as the date (it's virtually the same as the date). What I can't seem to do is combine the selected period and the grouping. I already saw the answers you linked (beside the fact that some are in js) – Federico Cupellini Aug 06 '17 at 03:19
  • The data in your question is very clear. It's a "string" and not a `Date`, so queries trying to match a `Date` type do not work. There are four questions with various answers that you really need to read and understand. One of them shows how to **convert** your "strings" into `BSON Date`. The others cover everything from basic date selection as a range ( which is essentially the part you are asking ) to actually "grouping" on the "partial value" of any given date. First call here is "fix your data" – Neil Lunn Aug 06 '17 at 03:23

0 Answers0