Need to aggregate by hour and $avg in MongoDB

To aggregate data by hour and calculate the average in MongoDB, use the aggregate() method with $group stage. The $hour operator extracts the hour from a date field, and $avg calculates the average value.

Syntax

db.collection.aggregate([
    {
        $group: {
            "_id": { "$hour": "$dateField" },
            "fieldName": { "$avg": "$numericField" }
        }
    }
]);

Sample Data

db.demo544.insertMany([
    { "DueTime": new ISODate("2020-01-10 12:10:20"), "Amount": 100 },
    { "DueTime": new ISODate("2020-01-12 12:00:00"), "Amount": 500 },
    { "DueTime": new ISODate("2020-01-12 12:10:20"), "Amount": 900 }
]);

{
    "acknowledged": true,
    "insertedIds": [
        ObjectId("5e8e1f029e5f92834d7f05ce"),
        ObjectId("5e8e1f089e5f92834d7f05cf"),
        ObjectId("5e8e1f109e5f92834d7f05d0")
    ]
}

Display all documents from the collection ?

db.demo544.find();

{ "_id": ObjectId("5e8e1f029e5f92834d7f05ce"), "DueTime": ISODate("2020-01-10T12:10:20Z"), "Amount": 100 }
{ "_id": ObjectId("5e8e1f089e5f92834d7f05cf"), "DueTime": ISODate("2020-01-12T12:00:00Z"), "Amount": 500 }
{ "_id": ObjectId("5e8e1f109e5f92834d7f05d0"), "DueTime": ISODate("2020-01-12T12:10:20Z"), "Amount": 900 }

Example: Aggregate by Hour with $avg

Calculate the average amount for each hour ?

db.demo544.aggregate([
    {
        $group: {
            "_id": { "$hour": "$DueTime" },
            "AverageAmount": { "$avg": "$Amount" }
        }
    }
]);

{ "_id": 12, "AverageAmount": 500 }

How It Works

• $hour extracts the hour (0-23) from the DueTime field
• $group groups documents by the extracted hour value
• $avg calculates the average of the Amount field for each group
• All documents have hour 12, so they are grouped together with average: (100 + 500 + 900) ÷ 3 = 500

Conclusion

Use $hour with $group and $avg to aggregate data by hour and calculate average values. This is useful for time-based analytics and hourly reporting in MongoDB.