将 mongodb 聚合管道中的毫秒数转换为 group by 的日期? [英] convert milliseconds to date in mongodb aggregation pipeline for group by?
问题描述
我必须在 mongodb 聚合管道中将毫秒转换为日期格式 -
I have to convert milliseconds to date format in mongodb aggregation pipiline -
我的查询是 -
db.campaign_wallet.aggregate({"$match" : {"campaignId" : 1, "txnTime" : { "$gte" : 1429554600000, "$lte" : 1430159400000}}}, {"$group" : {"_id" : {"msisdn" : "$msisdn", "txnTime" : "$txnTime"}, "count" : {"$sum": 1}}});
在此查询中如何将 txnTime(以毫秒为单位)转换为管道中的日期?
In this query how to convert txnTime (which is in milliseconds) to date in pipeline ?
推荐答案
我正在尝试获取将 txnTime
字段转换为日期对象背后的逻辑,因为按日期字段或以毫秒为单位的时间戳(就像您目前正在做的事情)将产生相同的结果,因为它们在各自的格式中都是独一无二的!
I'm trying to get the logic behind converting the txnTime
field to a date object because grouping by either a date field or a timestamp in milliseconds (like what you are presently doing) will yield the same result as they both are unique in their respective formats!
要将 txnTime
字段更改为日期对象,您应该包含一个 $project
管道在 $group
带有这个表达式的管道阶段
To change the txnTime
field to a date object you should then include a $project
pipeline before the $group
pipeline stage with this expression
"txnTime": {
"$add": [ new Date(0), "$txnTime" ]
}
这样你就可以做你的$group
对转换/投影的 txnTime 字段的操作:
so that you can do your $group
operation on the converted/projected txnTime field:
var convertedTxnTime = { "$add": [new Date(0), "$txnTime"] };
/*
If using MongoDB 4.0 and newer, use $toDate
var convertedTxnTime = { "$toDate": "$txnTime" };
or $convert
var convertedTxnTime = { "$convert": { "input": "$txnTime", "to": "date" } };
*/
db.campaign_wallet.aggregate([
{ "$match": {
"campaignId" : 1 ,
"txnTime" : {
"$gte" : 1429554600000 ,
"$lte" : 1430159400000
}
} },
{ "$group" : {
"_id" : {
"txnTime": convertedTxnTime,
"msisdn" : "$msisdn"
},
"msisdnCount" : { "$sum" : 1}
} }
]);
输出:(基于此问题)
Output: (based on the sample documents from this question)
/* 0 */
{
"result" : [
{
"_id" : {
"txnTime" : ISODate("2015-04-25T18:30:00.000Z"),
"msisdn" : "91808770101"
},
"msisdnCount" : 1
},
{
"_id" : {
"txnTime" : ISODate("2015-04-27T05:11:54.796Z"),
"msisdn" : "9180877010"
},
"msisdnCount" : 1
},
{
"_id" : {
"txnTime" : ISODate("2015-04-25T18:30:01.111Z"),
"msisdn" : "91808070101"
},
"msisdnCount" : 1
},
{
"_id" : {
"txnTime" : ISODate("2015-04-25T18:30:00.000Z"),
"msisdn" : "91808070101"
},
"msisdnCount" : 2
},
{
"_id" : {
"txnTime" : ISODate("2015-04-27T05:11:54.796Z"),
"msisdn" : "9189877000"
},
"msisdnCount" : 1
},
{
"_id" : {
"txnTime" : ISODate("2015-04-27T05:11:54.796Z"),
"msisdn" : "9189877667"
},
"msisdnCount" : 1
}
],
"ok" : 1
}
-- 更新 --
要使用 YYYY-MM-DD
格式按日期对文档进行分组,请使用 日期聚合运算符
To group the documents by date with the format YYYY-MM-DD
, use the Date Aggregation Operators
示例:
var convertedTxnTime = { "$add": [new Date(0), "$txnTime"] };
/*
If using MongoDB 4.0 and newer, use $toDate
var convertedTxnTime = { "$toDate": "$txnTime" };
or $convert
var convertedTxnTime = { "$convert": { "input": "$txnTime", "to": "date" } };
*/
db.campaign_wallet.aggregate([
{ "$match": {
"campaignId" : 1 ,
"txnTime" : {
"$gte" : 1429554600000 ,
"$lte" : 1430159400000
}
} },
{ "$group" : {
"_id" : {
"txnTime_year" : { "$year": convertedTxnTime },
"txnTime_month" : { "$month": convertedTxnTime },
"txnTime_day" : { "$dayOfMonth": convertedTxnTime },
"msisdn": "$msisdn"
},
"msisdnCount" : { "$sum" : 1}
} }
]);
输出:
/* 0 */
{
"result" : [
{
"_id" : {
"txnTime_year" : 2015,
"txnTime_month" : 4,
"txnTime_day" : 25,
"msisdn" : "91808770101"
},
"msisdnCount" : 1
},
{
"_id" : {
"txnTime_year" : 2015,
"txnTime_month" : 4,
"txnTime_day" : 25,
"msisdn" : "91808070101"
},
"msisdnCount" : 3
},
{
"_id" : {
"txnTime_year" : 2015,
"txnTime_month" : 4,
"txnTime_day" : 27,
"msisdn" : "9180877010"
},
"msisdnCount" : 1
},
{
"_id" : {
"txnTime_year" : 2015,
"txnTime_month" : 4,
"txnTime_day" : 27,
"msisdn" : "9189877000"
},
"msisdnCount" : 1
},
{
"_id" : {
"txnTime_year" : 2015,
"txnTime_month" : 4,
"txnTime_day" : 27,
"msisdn" : "9189877667"
},
"msisdnCount" : 1
}
],
"ok" : 1
}
这篇关于将 mongodb 聚合管道中的毫秒数转换为 group by 的日期?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!