CODEWITHSUNDEEP
phpmongodb
Henkealg
Henkealg

Reputation: 1516

Mongodb aggregate group by day (php)

I have a collection with documents that look like this:

{
    _id: ObjectId("516eb5d2ef4501a804000000"),
    accountCreated: "2013-04-17 16:46",
    accountLevel: 0,
    responderCount: 0
}

I want to group and count these documents based on the accountCreated date (count per day), but I am stuck with the handling of dates since the date includes time as well. This is what I have, but it returns the count including the time, witch means lots of entries always with 1 as accounts.

$g = $form->mCollectionUsers->aggregate(array(
    array( '$group' => array( '_id' => '$accountCreated', 'accounts' => array( '$sum' => 1 ) ) )
));

Is there a way to rewrite the date to only take day in account and skip the time?

I have found this example but I can´t really get figure out how to adapt it to this example.

Upvotes: 3

Views: 4054

Answers (2)

Miko Chu
Miko Chu

Reputation: 1382

If you want to display the date properly:

db.mCollectionUsers.aggregate([
    {
        $group: {
            _id: { $dateToString: { format: '%Y-%m-%d', date: '$accountCreated' } },
            count: { $sum: 1 }
        }
    },
    {
        $project: {
            _id: 0,
            date: '$_id',
            count: 1
        }
    }
])

The result would look like:

[
  {
    "date": "2020-11-11",
    "count": 8
  },
  {
    "date": "2020-11-13",
    "count": 3
  },
  {
    "date": "2020-11-16",
    "count": 3
  },
]

Upvotes: 1

joao
joao

Reputation: 4117

If accountCreated is a date you can do it like this (I'll use the mongo shell syntax since I'm not familiar with the php driver):

db.mCollectionUsers.aggregate([
    {$project :{
        day : {"$dayOfMonth" : "$accountCreated"},
        month : {"$month" : "$accountCreated"},
        year : {"$year" : "$accountCreated"}
    }},
    {$group: {
        _id : {year : "$year", month : "$month", day : "$day"},
        accounts : { "$sum" : 1}
    }}
]);

Upvotes: 5

Related Questions