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?