CODEWITHSUNDEEP
mongodbmongooseaggregation-framework
Rob Walker
Rob Walker

Reputation: 47452

Aggregate data stored as time_t in MongoDB

I have a collection of documents stored in MongoDB which include a created field as a unix style time_t:

e.g. 

{"created": 1137459055216, "qty": 3 }

How can I aggregate this data by month through a query?

Upvotes: 1

Views: 93

Answers (1)

JohnnyHK
JohnnyHK

Reputation: 311835

You can convert unix epoch ms counts to native Date objects for aggregation by adding the count to new Date(0). For example:

db.test.aggregate([
    {$group: {
        _id: {$month: {$add: [new Date(0), '$created']}},
        qty: {$sum: '$qty'}
    }}
])

Or if you want aggregate by month and year, add a $project that does the conversion and then you can easily reference it multiple times in your $group:

db.test.aggregate([
    {$project: {
        createdDate: {$add: [new Date(0), '$created']},
        qty: '$qty'
    }},
    {$group: {
        _id: {
            year: {$year: '$createdDate'}, 
            month: {$month: '$createdDate'}
        },
        qty: {$sum: '$qty'}
    }}
])

Note that if created is a seconds count instead of a ms count, you also need to multiply it by 1000:

createdDate: {$add: [new Date(0), { $multiply: ['$created', 1000] }]}

Upvotes: 2

Related Questions