英文:
Calculate the number of days between 2 dates in Mongodb aggregation
问题
文档字段:
{
"oldDate": ISODate("2019-05-13T07:40:23.000Z"),
"neweDate": ISODate("2019-05-14T05:16:27.000Z"),
}
当我添加以下聚合管道时;
"daysSpent": {
"$trunc": {
"$divide": [
{
"$subtract": [
"$newDate",
"$oldDate"
]
},
1000 * 60 * 60 * 24
]
}
}
结果是
"daysSpent": 0
但我需要结果为1，因为这是两个不同的日期（不考虑时间）。
如何在两个日期之间找到天数而避免考虑时间？
<details>
<summary>英文:</summary>
Document fields:
{
"oldDate" : ISODate("2019-05-13T07:40:23.000Z"),
"neweDate" : ISODate("2019-05-14T05:16:27.000Z"),
}
When I add an aggregation pipeline as below;
"daysSpent": {
"$trunc": {
"$divide": [
{
"$subtract": [
"$newDate",
"$oldDate"
]
},
1000 * 60 * 60 * 24
]
}
}
the result is
"daysSpent" : 0
But I need the result as 1 as these are 2 different days (irrespective of time).
How can I avoid time while finding days between two dates?
</details>
# 答案1
**得分**: 1
你可以首先通过减去两个日期来找到`timestamp`，然后将整个方程除以以获取**天**、**小时**、**分钟**、**秒**和**毫秒**。
1 小时 = 60 分钟 = 60 × 60 秒 = 3600 秒 = 3600 × 1000 毫秒 = 3,600,000 毫秒。
聚合
db.collection.aggregate([
{ "$addFields": {
"timestamp": {
"$subtract": [ "$newDate", "$oldDate" ]
}
}},
{ "$addFields": {
"daysSpent": {
"$trunc": {
"$ceil": {
"$abs": {
"$sum": { "$divide": ["$timestamp", 60 * 1000 * 60 * 24] }
}
}
}
}
}}
])
<details>
<summary>英文:</summary>
You can first find the `timestamp` by subtracting both the dates and then divide the whole equation to get the **days**, **hours**, **minutes**, **seconds**, **milliseconds**.
1 hour = 60 minutes = 60 × 60 seconds = 3600 seconds = 3600 × 1000 milliseconds = 3,600,000 ms.
Aggregation
db.collection.aggregate([
{ "$addFields": {
"timestamp": {
"$subtract": [ "$newDate", "$oldDate" ]
}
}},
{ "$addFields": {
"daysSpent": {
"$trunc": {
"$ceil": {
"$abs": {
"$sum": { "$divide": ["$timestamp", 60 * 1000 * 60 * 24] }
}
}
}
}
}}
])
[**MongoPlayground**][1]
[1]: https://mongoplayground.net/p/zLkHGleYon4
</details>
# 答案2
**得分**: 1
```json
db.collection.aggregate([
{
$addFields: {
"days": {
$trunc: {
$divide: [
{
$subtract: ["$newDate", "$oldDate"]
},
1000 * 60 * 60 * 24
]
}
}
}
},
{
$project: {
_id: 0,
days: {
$toInt: {
$cond: {
if: {
$eq: ["$days", 0]
},
then: 1,
else: "$days"
}
}
}
}
}
])
英文:
db.collection.aggregate( [
{
$addFields: {
"days": {
$trunc : {
$divide: [{
$subtract: ["$newDate","$oldDate"]
}, 1000 * 60 * 60 * 24]
}
}
}
},{
$project: {
_id: 0,
days: {$toInt: {$cond: {if: {$eq: ["$days", 0]}, then: 1, else: "$days"}}},
}
}] )
<!-- end snippet -->
答案3
得分: 0
db.collection.aggregate([
{
"$addFields": {
"daysSpent": {
"$let": {
"vars": {
"roundDateNew": {
"$dateFromParts": {
"year": {
"$year": "$newDate"
},
"month": {
"$month": "$newDate"
},
"day": {
"$dayOfMonth": "$newDate"
}
}
},
"roundDateOld": {
"$dateFromParts": {
"year": {
"$year": "$oldDate"
},
"month": {
"$month": "$oldDate"
},
"day": {
"$dayOfMonth": "$oldDate"
}
}
}
},
"in": {
"$divide": [
{
"$subtract": [
"$$roundDateNew",
"$$roundDateOld"
]
},
86400000
]
}
}
}
}
}
])
英文:
db.collection.aggregate([
{
"$addFields": {
"daysSpent": {
"$let": {
"vars": {
"roundDateNew": {
"$dateFromParts": {
"year": {
"$year": "$newDate"
},
"month": {
"$month": "$newDate"
},
"day": {
"$dayOfMonth": "$newDate"
}
}
},
"roundDateOld": {
"$dateFromParts": {
"year": {
"$year": "$oldDate"
},
"month": {
"$month": "$oldDate"
},
"day": {
"$dayOfMonth": "$oldDate"
}
}
}
},
"in": {
"$divide": [
{
"$subtract": [
"$$roundDateNew",
"$$roundDateOld"
]
},
86400000
]
}
}
}
}
}
])
above aggregation worked for me.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论