How to convert milliseconds to date in mongodb aggregation?

Question

I have a collection in MongoDB and documents like this -

[
    {
        "campaignId": 1,
        "operatorId": 1,
        "txnType": "DR",
        "amount": 2,
        "balance": 0,
        "txnTime": 1428907779206,
        "txnSrc": "Dial_In",
        "msisdn": "9789877667",
        "circle": "Delhi",
        "smsContent": "Hello Mr Arif"
    },
    {
        "campaignId": 1,
        "operatorId": 1,
        "txnType": "DR",
        "circle": "Delhi",
        "amount": 2,
        "balance": 0,
        "txnTime": 1430111514796,
        "txnSrc": "Dial_In",
        "msisdn": "9189877667",
        "smsContent": "Hello Mr Arif"
    },
    {
        "campaignId": 1,
        "operatorId": 1,
        "txnType": "DR",
        "circle": "Delhi",
        "amount": 2,
        "balance": 0,
        "txnTime": 1430111514796,
        "txnSrc": "Dial_In",
        "msisdn": "9189877000",
        "smsContent": "Hello Mr Arif"
    },
    {
        "campaignId": 8,
        "operatorId": 1,
        "txnType": "DR",
        "circle": "Delhi",
        "amount": 2,
        "balance": 0,
        "txnTime": 1430111514796,
        "txnSrc": "Dial_In",
        "msisdn": "9189877010",
        "smsContent": "Hello Mr Arif"
    },
    {
        "campaignId": 8,
        "operatorId": 1,
        "txnType": "DR",
        "circle": "Mumbai",
        "amount": 2,
        "balance": 0,
        "txnTime": 1430111514796,
        "txnSrc": "Dial_In",
        "msisdn": "9180877010",
        "smsContent": "Hello Mr Arif"
    },
    {
        "campaignId": 1,
        "operatorId": 1,
        "txnType": "DR",
        "circle": "Mumbai",
        "amount": 2,
        "balance": 0,
        "txnTime": 1430111514796,
        "txnSrc": "Dial_In",
        "msisdn": "9180877010",
        "smsContent": "Hello Mr Arif"
    },
    {
        "campaignId": 1,
        "operatorId": 1,
        "txnType": "DR",
        "circle": "Mumbai",
        "amount": 2,
        "balance": 0,
        "txnTime": 1429986600000,
        "txnSrc": "Dial_In",
        "msisdn": "91808770101",
        "smsContent": "Hello Mr Arif"
    },
    {
        "campaignId": 1,
        "operatorId": 1,
        "txnType": "DR",
        "circle": "Delhi",
        "amount": 2,
        "balance": 0,
        "txnTime": 1429986600000,
        "txnSrc": "Dial_In",
        "msisdn": "91808070101",
        "smsContent": "Hello Mr Arif"
    },
    {
        "campaignId": 1,
        "operatorId": 1,
        "txnType": "DR",
        "circle": "Delhi",
        "amount": 2,
        "balance": 0,
        "txnTime": 1429986600000,
        "txnSrc": "Dial_In",
        "msisdn": "91808070101",
        "smsContent": "Hello Mr Arif"
    },
    {
        "campaignId": 8,
        "operatorId": 1,
        "txnType": "DR",
        "circle": "Jaipur",
        "amount": 2,
        "balance": 0,
        "txnTime": 1430111514796,
        "txnSrc": "Dial_In",
        "msisdn": "9180877010",
        "smsContent": "Hello Mr Arif"
    },
    {
        "campaignId": 8,
        "operatorId": 1,
        "txnType": "DR",
        "circle": "UP-West",
        "amount": 2,
        "balance": 0,
        "txnTime": 1430111514796,
        "txnSrc": "Dial_In",
        "msisdn": "9180877010",
        "smsContent": "Hello Mr Arif"
    },
    {
        "campaignId": 1,
        "operatorId": 1,
        "txnType": "DR",
        "circle": "Delhi",
        "amount": 2,
        "balance": 0,
        "txnTime": 1429986601111,
        "txnSrc": "Dial_In",
        "msisdn": "91808070101",
        "smsContent": "Hello Mr Arif"
    }
]

I made a aggregation query for group by for this collection on the basis of unique msisdn for a date, which is -

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

This is giving right result on the basis of time in milliseconds and msisdns -

Query Result

I have to convert time(milliseconds) in date in my query so that it will filter data on the basis of date not on exact time in milliseconds. What is the solution?

@Philipp I edited question. I want to change time miliseconds to date in the mongodb aggregation query. — Amit Das, Apr 27 '15 at 07:36

score 3 · Accepted Answer · answered Apr 27 '15 at 08:02

You could try adding the milliseconds time to a zero-milliseconds Date() object in the $project operator using the $add arithmetic operator, so an aggregation pipeline like the following will give you the timestamp field converted to Date:

db.campaign_wallet.aggregate([
    { 
        "$match": { 
            "campaignId" : 1 , 
            "txnTime" : { 
                "$gte" : 1429554600000 , 
                "$lte" : 1430159400000
            }
        }
    },
    { 
        "$group" : { 
            "_id" : {
                "txnTime" : "$txnTime",
                "msisdn":"$msisdn"
            }, 
            "msisdnCount" : { "$sum" : 1}
        }
    },
    { 
        "$group" : { 
            "_id" : "$_id.txnTime", 
            "msisdns" : { 
                "$push" :{
                    "txnTime" : "$_id.txnTime", 
                    "count" : "$msisdnCount"
                },
            }, 
            "count" : { 
                "$sum" : "$msisdnCount"
            }
        }
    },
    {
        "$unwind": "$msisdns"
    },
    {
        "$project": {
            "msisdns": {
                "txnTime" : {
                    "$add": [ new Date(0), "$msisdns.txnTime" ]
                }
            },
            "msisdns.count": 1,
            "count": 1
         } 
    }
]);

Output:

/* 0 */
{
    "result" : [ 
        {
            "_id" : 1430111514796,
            "msisdns" : {
                "txnTime" : ISODate("2015-04-27T05:11:54.796Z"),
                "count" : 1
            },
            "count" : 1
        }, 
        {
            "_id" : 1430111514900,
            "msisdns" : {
                "txnTime" : ISODate("2015-04-27T05:11:54.900Z"),
                "count" : 1
            },
            "count" : 1
        }
    ],
    "ok" : 1
}

@AmitDas I simply copied your aggregation pipeline, added the `$unwind` and `$project` pipeline stages and ran it against the sample documents that you provided in your question above, got those results with the timestamp field converted. Are you running it against a sample/test collection? Perhaps the `$match` query is not filtering the documents correctly? I really am not sure at this point what might be causing the wrong result. — chridam, Apr 27 '15 at 08:40
@AmitDas I have completely missed your point since you are now asking for a different thing altogether here. In your comment reply to Phillip, you said _I want to change time miliseconds to date in the mongodb aggregation query_ and the answer I gave addresses that, can you please be more explicit or clearer with your question? — chridam, Apr 27 '15 at 08:42

How to convert milliseconds to date in mongodb aggregation?

1 Answers1

Linked