aggregate group by time of intervals of 15 minutes

Question

I'm trying to calculate average speed of data for 15 minutes. I get the result back, it contains average speed, but not sure it's correct and for 15 minute sets, also minutes is nil.

o3 := bson.M{
    "$group": bson.M{
        "_id": bson.M{
            "minute": bson.M{
                "$subtract": []interface{}{
                    "$timestamp",
                    bson.M{
                        "$mod": []interface{}{
                            "$minute",
                            15,
                        },
                    },
                },
            },
        },
        "averageSpeed": bson.M{
            "$avg": "$speed",
        },
    },
}

Anyone done something similar or can help?

EDIT: $timestamp field is ISODate format and Date type

Thank you

It seems you got the code from that question but you misread the `$minute` which is acutally an aggregation operator, where you have interpreted it as a field. So the BSON here does not match the form, and why you don't get the correct result. — Blakes Seven, Mar 08 '16 at 09:27
I got code from "duplicate" question, some other sources and mongodb manual - so I made my own version with all those :). — user2343398, Mar 08 '16 at 21:49
And that is where you went wrong. By following the example you would have arrived at the correct interval. You only got an incorrect result because you deviated from what was there and misinterpreted the purpose of the operators used. That's the lesson. — Blakes Seven, Mar 08 '16 at 22:04
Well I had trouble also translating those structure to GO correctly, which caused creation of my version. Getting `{ "$mod": [{ "$minute": "$timestamp", 15 }] }` to `bson.M{ "$mod": []interface{}{ bson.M{"$minute": "$timestamp",},5,},},` wasn't so easy for me. Hope this helps to somebody else also. — user2343398, Mar 08 '16 at 23:01

chridam · Accepted Answer · 2016-09-28T13:03:31.583

Running the following pipeline in mongo shell should give you the correct result:

pipeline = [
    { 
        "$group": {
            "_id": {
                "year": { "$year": "$timestamp" },
                "dayOfYear": { "$dayOfYear": "$timestamp" },
                "minute_interval": {
                    "$subtract": [ 
                        { "$minute": "$timestamp" },
                        { "$mod": [{ "$minute": "$timestamp" }, 15] }
                    ]
                }
            },
            "averageSpeed": { "$avg": "$speed" }
        }
    }
]
db.collection.aggregate(pipeline)

of which the equivalent mGo expression follows (untested):

pipeline := []bson.D{   
    bson.M{
        "$group": bson.M{
            "_id": bson.M{
                "year": bson.M{ "$year": "$timestamp" },
                "dayOfYear": bson.M{ "$dayOfYear": "$timestamp" },
                "minute_interval": bson.M{
                    "$subtract": []interface{}{ 
                        bson.M{ "$minute": "$timestamp" },
                        bson.M{ "$mod": []interface{}{ bson.M{ "$minute": "$timestamp" }, 15, }, } }
                    }
                }
            },
            "averageSpeed": bson.M{ "$avg": "$speed" }
        }
    }
}

pipe := collection.Pipe(pipeline)
iter := pipe.Iter()

Noting the lacking citation to where the code comes from. Being another question with pretty much the same title: [" Group result by 15 minutes time interval in MongoDb"](http://stackoverflow.com/questions/26814427/group-result-by-15-minutes-time-interval-in-mongodb) — Blakes Seven, Mar 08 '16 at 09:27
note: `{ "$mod": [{ "$minute": "$timestamp", 15 }] }` is `bson.M{ "$mod": []interface{}{ bson.M{"$minute": "$timestamp",},5,},},` in golang — user2343398, Mar 09 '16 at 07:40
totally copied from https://stackoverflow.com/questions/26814427/group-result-by-15-minutes-time-interval-in-mongodb — user3896501, Mar 16 '20 at 17:31

aggregate group by time of intervals of 15 minutes

1 Answers1