如何在 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"
}
}
});
这是基于以毫秒和 msisdns 为单位的时间给出正确的结果 -
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?
推荐答案
您可以尝试将毫秒时间添加到 $project 运算符使用 $add 算术运算符，所以像下面这样的聚合管道将为您提供转换为日期的时间戳字段:
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屋！
查看全文
