如何在mongodb聚合中将毫秒转换为日期? [英] How to convert milliseconds to date in mongodb aggregation?
本文介绍了如何在mongodb聚合中将毫秒转换为日期?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我在MongoDB中有一个集合，像这样的文档-
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"
}
]
我根据唯一的日期msisdn对该集合的group by进行了聚合查询，该日期是-
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?
推荐答案
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
}
}
]);
输出:
/* 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
}
这篇关于如何在mongodb聚合中将毫秒转换为日期?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
