How to convert milliseconds to date in mongodb agg

2019-02-18 15:45发布

问题:

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 -

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?

回答1:

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
}