Hy, I have some documents with a start date and a end date.
{
startDate: ISODate("2020-01-07T00:00:00.000Z"),
endDate: ISODate("2020-01-10T00:00:00.000Z")
}
Is it possible using mongodb aggregations to have something like
[
ISODate("2020-01-07T00:00:00.000Z"),
ISODate("2020-01-08T00:00:00.000Z"),
ISODate("2020-01-09T00:00:00.000Z"),
ISODate("2020-01-10T00:00:00.000Z")
]
The goal is to intersect two date range in order to extract common date.
Thanks a lot
You may use $range (similar to for loop) operator. Main idea:
for(i=0; i<difference(endDate - startDate); i++){
startDate + (i * `24 * 60 * 60 * 1000`)
}
difference(endDate - startDate)returns time differences in milliseconds, so we need to divide24 * 60 * 60 * 1000
db.collection.aggregate([
{
$addFields: {
range: {
$range: [
0,
{
$add: [
{
$divide: [
{
$subtract: [
"$endDate",
"$startDate"
]
},
{
$multiply: [
24,
60,
60,
1000
]
}
]
},
1
]
},
1
]
}
}
},
{
$project: {
_id: 0,
result: {
$map: {
input: "$range",
in: {
$add: [
"$startDate",
{
$multiply: [
"$$this",
24,
60,
60,
1000
]
}
]
}
}
}
}
}
])
{
"result" : [
ISODate("2020-01-07T00:00:00.000Z"),
ISODate("2020-01-08T00:00:00.000Z"),
ISODate("2020-01-09T00:00:00.000Z"),
ISODate("2020-01-10T00:00:00.000Z")
]
}