To find total amount of weekdays between 2 dates you can subtract total amount of weekends from total amount of days. The main problem here is to find total amount of weekends.
Example [2016-12-23; 2017-01-01]
_Dec23___Dec24___Dec25____... ____Dec30___Dec31___Jan01___...
__Fri______Sat_____Sun______...______Fri_____Sat_____Sun_____...
As you see, there are 4 weekends between those dates (Dec24, Dec25, Dec31 and Jan01).
Solution
One way to solve it programmatically is to use aggregation framework with its arithmetic and date aggregation operators.
It could be done with splitting the task into 2 subtasks:
- Find amount of full weeks between
startDate and endDate (name it weeksBetween). In the above example weeksBetween = 1.
- Find amount of full days between
startDate + 7 * weeksBetween and endDate (name it left, it could be 1,2,3,4,5 or 6). In the above example left = 3 (left days are Fri, Sat and Sun). Of interest are amount of weekends in those left days (name it left_weekends, it could be 0,1 or 2). In the above example left_weekends = 2.
Then, total amount of weekends is 2 * weeksBetween + left_weekends. In the above example it's 2 * 1 + 2 = 4.
To find left_weekends you can use $dayOfWeek date aggregation operator.
- for Sun
dayOfWeek = 1 and left_weekends = 1 if left is 1,2,3,4,5 or left_weekends = 2 if left is 6 (including Sun and next Sat).
- for Mon
dayOfWeek = 2 and left_weekends = 0 if left is 1,2,3,4 or left_weekends = 1 if left is 5 or left_weekends = 2 if left is 6 (including next Sat and Sun).
...
- for Sat
dayOfWeek = 7 and left_weekends = 2 for any left.
Hence, there are next rules we can use:
If remainder > 7, then left_weekends = 2.
If remainder = 7, then left_weekends = 1 for Mon-Sat and left_weekends = 2 for Sun.
If remainder < 7 then left_weekends = 0 for Mon-Sat and left_weekends = 1 for Sun.
Here, remainder = dayOfWeek + left.
Query
db.dates.aggregate(
{
$project:
{
"daysBetween":
{
$add: [
{
$floor:
{
$divide: [
{ $subtract: [ "$endDate", "$startDate" ] },
1000 * 60 * 60 * 24
]
}
},
1
]
},
"startDay": { $dayOfWeek: "$startDate" },
"endDay": { $dayOfWeek: "$endDate" }
}
},
{
$project:
{
"daysBetween": "$daysBetween",
"weeksBetween": { $floor: { $divide: [ "$daysBetween", 7 ] } },
"startDay": "$startDay",
"remainder":
{
$add: [
{ $abs: { $subtract: [ "$endDay", "$startDay" ] } },
"$startDay"
]
}
}
},
{
$project:
{
"weekendsBetween":
{
$add: [
{ $multiply: [ "$weeksBetween", 2 ] },
{
$cond:
{
if: { $gt: [ "$remainder", 7 ] },
then: 2,
else:
{
$cond:
{
if: { $eq: [ "$remainder", 7 ] },
then: 1,
else: 0
}
}
}
},
{ $cond: { if: { $eq: [ "$startDay", 1 ] }, then: 1, else: 0 } }
]
},
"daysBetween": "$daysBetween"
}
},
{
$project:
{
"weekdaysBetween": { $subtract: [ "$daysBetween", "$weekendsBetween" ] }
}
}
);
Sample
{ "_id": 1, "startDate": ISODate("2016-12-23T00:00:00Z"),
"endDate": ISODate("2017-01-01T00:00:00Z") }
{ "_id": 2, "startDate": ISODate("2016-12-23T00:00:00Z"),
"endDate": ISODate("2017-01-09T00:00:00Z") }
{ "_id": 3, "startDate": ISODate("2016-12-23T00:00:00Z"),
"endDate": ISODate("2017-01-18T00:00:00Z") }
Results
{ "_id": 1, "weekdaysBetween" : 6 }
{ "_id": 2, "weekdaysBetween" : 12 }
{ "_id": 3, "weekdaysBetween" : 19 }
PS Extending last $project stage you can also get total amounts of days and weekends between 2 dates. Don't forget to change collection name before testing the query.
