mongodb - 如何获取mongodb聚合中两个日期之间的工作日数

Question

我有两个日期开始日期:2019-08-13和结束日期:2019-08-20。我希望结束日期与开始日期之间的差异工作日(不包括假期)。所以我的结果将是 4，因为 2019-08-15 是假期，2019-08-17 >，2019-08-18是周末。

Answer 1

尝试这个查询:

db.collection.aggregate([
{$addFields: {days_in_millis: {  $add: [{$subtract: ["$end_date", "$start_date"]}, 86400000] } }},
{$project: {end_date: 1, start_date: 1, millis_range: {$range: [0, "$days_in_millis", 86400000 ] } } },
{$project: {dates_in_between_inclusive: {
    $map: {
        input: "$millis_range",
        as: "millis_count",
        in: {$add: ["$start_date", "$$millis_count"]}
    }
}}},
{$unwind: "$dates_in_between_inclusive"},
{$project: {date: "$dates_in_between_inclusive", is_not_weekend: {
    $cond: {
    if: { $in: [ {$dayOfWeek: "$dates_in_between_inclusive"}, [1, 7] ]},
    then: 0,
    else: 1
}}}},
{$match: {date: {$nin: holidays_dates_list}}},
{$group: {
    _id: "$_id",
    days: {$sum: "$is_not_weekend"}
}}
])

假设:

1. every document has at least start_date and end_date fields which are mongodb dates.
2. "holidays_dates_list" is an array of dates which has holidays (may or may not include weekends)

以上查询本身过滤周末。因此，“holidays_dates_list”不需要有周末。