convert milliseconds to date in mongodb aggregation pipeline for group by?

Question

I have to convert milliseconds to date format in mongodb aggregation pipiline -

My query is -

db.campaign_wallet.aggregate({"$match" : {"campaignId" : 1, "txnTime" : { "$gte" : 1429554600000, "$lte" : 1430159400000}}}, {"$group" : {"_id" : {"msisdn" : "$msisdn", "txnTime" : "$txnTime"}, "count" : {"$sum": 1}}});

In this query how to convert txnTime (which is in milliseconds) to date in pipeline ?

possible duplicate of [How to convert miliseconds to date in mongodb aggregation?](http://stackoverflow.com/questions/29889719/how-to-convert-miliseconds-to-date-in-mongodb-aggregation) — chridam, Apr 27 '15 at 09:48
@chridam This is new question and in this i have to convert miliseconds to date in the pipeline....can you solve it ? — Amit Das, Apr 27 '15 at 09:52
@AmitDas Please post actual text of the query instead of a screenshot. — ZeMoon, Apr 27 '15 at 09:54
@AmitDas At which pipeline stage do you want that field converted? If you want to filter the documents based on some date range then use the projection in my answer to this [**duplicate**](http://stackoverflow.com/questions/29889719/how-to-convert-miliseconds-to-date-in-mongodb-aggregation) as the first pipeline stage then do the `$match` and `$group` operations based on the projected date field. — chridam, Apr 27 '15 at 10:05
possible duplicate of [In MongoDB $project clause, how can I convert date from milliseconds to ISODate object](http://stackoverflow.com/questions/22698265/in-mongodb-project-clause-how-can-i-convert-date-from-milliseconds-to-isodate) — ZeMoon, Apr 27 '15 at 10:05
@chridam I want to group by msisdn feild and txnTime feild to achieve unique msisdn with unique date — Amit Das, Apr 27 '15 at 10:10
@AmitDas Help me to understand your objective here; won't the timestamp field alone give you a unique date? Converting milliseconds to a date will be an overhead because the timestamp in milliseconds is essentially a datetime value. — chridam, Apr 27 '15 at 10:19
@chridam .... I have to change milliseconds to date format for "txnTime" feild in the groupby pipeline. In my collection , the "txnTime" feild is in milliseconds. — Amit Das, Apr 27 '15 at 10:23
@chridam Only if it is milliseconds difference to a fixed date. ;) though it looks like msecs since epoch, we can't be sure of that. — Markus W Mahlberg, Apr 28 '15 at 08:29
@MarkusWMahlberg So true, how a milisecond difference in datetime will make a huge impact in the uniqueness of a field :-) — chridam, Apr 28 '15 at 08:32

chridam · Accepted Answer · 2018-10-12T15:54:09.870

I'm trying to get the logic behind converting the txnTime field to a date object because grouping by either a date field or a timestamp in milliseconds (like what you are presently doing) will yield the same result as they both are unique in their respective formats!

To change the txnTime field to a date object you should then include a $project pipeline before the $group pipeline stage with this expression

"txnTime": {
    "$add": [ new Date(0), "$txnTime" ]
}

so that you can do your $group operation on the converted/projected txnTime field:

var convertedTxnTime = { "$add": [new Date(0), "$txnTime"] };

/*
  If using MongoDB 4.0 and newer, use $toDate 

  var convertedTxnTime = { "$toDate": "$txnTime" };

  or $convert

  var convertedTxnTime = { "$convert": { "input": "$txnTime", "to": "date" } };

*/

db.campaign_wallet.aggregate([
    { "$match": { 
        "campaignId" : 1 , 
        "txnTime" : { 
            "$gte" : 1429554600000 , 
            "$lte" : 1430159400000
        }
    } },
    { "$group" : { 
        "_id" : {
            "txnTime": convertedTxnTime,
            "msisdn" : "$msisdn"
        }, 
        "msisdnCount" : { "$sum" : 1}
    } }
]);

Output: (based on the sample documents from this question)

/* 0 */
{
    "result" : [ 
        {
            "_id" : {
                "txnTime" : ISODate("2015-04-25T18:30:00.000Z"),
                "msisdn" : "91808770101"
            },
            "msisdnCount" : 1
        }, 
        {
            "_id" : {
                "txnTime" : ISODate("2015-04-27T05:11:54.796Z"),
                "msisdn" : "9180877010"
            },
            "msisdnCount" : 1
        }, 
        {
            "_id" : {
                "txnTime" : ISODate("2015-04-25T18:30:01.111Z"),
                "msisdn" : "91808070101"
            },
            "msisdnCount" : 1
        }, 
        {
            "_id" : {
                "txnTime" : ISODate("2015-04-25T18:30:00.000Z"),
                "msisdn" : "91808070101"
            },
            "msisdnCount" : 2
        }, 
        {
            "_id" : {
                "txnTime" : ISODate("2015-04-27T05:11:54.796Z"),
                "msisdn" : "9189877000"
            },
            "msisdnCount" : 1
        }, 
        {
            "_id" : {
                "txnTime" : ISODate("2015-04-27T05:11:54.796Z"),
                "msisdn" : "9189877667"
            },
            "msisdnCount" : 1
        }
    ],
    "ok" : 1
}

-- UPDATE --

To group the documents by date with the format YYYY-MM-DD, use the Date Aggregation Operators

Example:

var convertedTxnTime = { "$add": [new Date(0), "$txnTime"] };

/*
  If using MongoDB 4.0 and newer, use $toDate 

  var convertedTxnTime = { "$toDate": "$txnTime" };

  or $convert

  var convertedTxnTime = { "$convert": { "input": "$txnTime", "to": "date" } };

*/

db.campaign_wallet.aggregate([
    { "$match": { 
        "campaignId" : 1 , 
        "txnTime" : { 
            "$gte" : 1429554600000 , 
            "$lte" : 1430159400000
        }
    } },
    { "$group" : { 
        "_id" : {
            "txnTime_year" : { "$year": convertedTxnTime },
            "txnTime_month" : { "$month": convertedTxnTime },
            "txnTime_day" : { "$dayOfMonth": convertedTxnTime },
            "msisdn": "$msisdn"
        }, 
        "msisdnCount" : { "$sum" : 1}
    } }
]);

Output:

/* 0 */
{
    "result" : [ 
        {
            "_id" : {
                "txnTime_year" : 2015,
                "txnTime_month" : 4,
                "txnTime_day" : 25,
                "msisdn" : "91808770101"
            },
            "msisdnCount" : 1
        }, 
        {
            "_id" : {
                "txnTime_year" : 2015,
                "txnTime_month" : 4,
                "txnTime_day" : 25,
                "msisdn" : "91808070101"
            },
            "msisdnCount" : 3
        }, 
        {
            "_id" : {
                "txnTime_year" : 2015,
                "txnTime_month" : 4,
                "txnTime_day" : 27,
                "msisdn" : "9180877010"
            },
            "msisdnCount" : 1
        }, 
        {
            "_id" : {
                "txnTime_year" : 2015,
                "txnTime_month" : 4,
                "txnTime_day" : 27,
                "msisdn" : "9189877000"
            },
            "msisdnCount" : 1
        }, 
        {
            "_id" : {
                "txnTime_year" : 2015,
                "txnTime_month" : 4,
                "txnTime_day" : 27,
                "msisdn" : "9189877667"
            },
            "msisdnCount" : 1
        }
    ],
    "ok" : 1
}

this is almost right .... but msisdn = "91808070101" was on same date ....so its entry in the result should be like this - { "_id" : { "txnTime" : ISODate("2015-04-25"), "msisdn" : "91808070101" }, "msisdnCount" : 3 }, — Amit Das, Apr 27 '15 at 11:13
@AmitDas I've updated the answer to this effect. It would be great if you could be more elaborate in your questions, be specific with the objectives as I couldn't really understand what you wanted in the first place. I eventually got it that you want to group the documents by a date with the format `YYYY-MM-DD` as you finally mentioned in your comments. — chridam, Apr 27 '15 at 11:54

Ashh · Answer 2 · 2019-05-22T09:53:22.160

4

With mongodb 4.0 you can try $toDate aggregation to convert milliseconds to date format

db.collection.aggregate([
  { "$match": { 
    "campaignId" : 1 , 
    "txnTime" : { 
      "$gte" : 1429554600000 , 
      "$lte" : 1430159400000
    }
  }},
  { "$project": {
    "toDate": {
      "$toDate": "$txnTime"
    }
  }}
])

You can try it here

edited May 22 '19 at 09:53

answered Jul 27 '18 at 21:58

Ashh

44,693
14
105
132

convert milliseconds to date in mongodb aggregation pipeline for group by?

2 Answers2

Linked