Issues using $isoDayOfWeek with logical operators: $gte, $or, $lte

I have the following $project code:

current_day: {$isoDayOfWeek: “$time_stamp_sign_on_ISODate”}

{$project: {current_day: 1, total_work_time_hours_base10_rounded: 1, total_worktime_seconds: 1, weekday: { $or: [ { $gte: [ “$current_day”, 1 ] },
{ $lte: [ “$current_day”, 5 ] } ] }, weekend_day: { $or: [ { $eq: [ “$current_day”, 6 ] },

{ $eq: [ “$current_day”, 7 ] } ] }}

}

The code should classify current_day as a weekend_day when the $isoDayOfWeek value is either 6 or 7. The current_day should be classified as a weekday if the $isoDayOfWeek value is between 1 and 5.

The query is able to print the correct $isoDayOfWeek value, but the logic implemented with - $gte, $lte, $or - fails.

Do you have any ideas about what could be wrong?

Cheers,
Daniel

The weekday criteria needs to be an inclusive range, so changing the $or to an $and should produce the desired result.

For example:

db.foo.drop();
db.foo.insertMany([
  { current_day: 1 },
  { current_day: 2 },
  { current_day: 3 },
  { current_day: 4 },
  { current_day: 5 },
  { current_day: 6 },
  { current_day: 7 }
])
db.foo.aggregate([
{
  $project: {
    _id: 0, current_day: 1,
    weekday: { $and: [{ $gte: ["$current_day", 1] }, { $lte: ["$current_day", 5] } ] },
    weekend_day: { $or: [{ $eq: ["$current_day", 6] }, { $eq: ["$current_day", 7] } ] }
  }
}]);
// output
[
  {
    "current_day": 1,
    "weekday": true,
    "weekend_day": false
  },
  {
    "current_day": 2,
    "weekday": true,
    "weekend_day": false
  },
  {
    "current_day": 3,
    "weekday": true,
    "weekend_day": false
  },
  {
    "current_day": 4,
    "weekday": true,
    "weekend_day": false
  },
  {
    "current_day": 5,
    "weekday": true,
    "weekend_day": false
  },
  {
    "current_day": 6,
    "weekday": false,
    "weekend_day": true
  },
  {
    "current_day": 7,
    "weekday": false,
    "weekend_day": true
  }
]

By having the weekday filter include an $or you’re matching ANY value in the range >= 1 OR <= 5. Since 6 and 7 are >= 1 you’d be matching EVERY possible value greater than 1.

/// This works with all fields added!
db.WorkTimeData.aggregate([{$match : { “time_stamp_sign_on_ISODate”: { $gte: new Date(“2020-01-01:00:00:00”), $lte: new Date(“2022-12-31:23:59:59”) } }},{$group: {
_id: { id: “$_id”,
current_year: {$year: “$time_stamp_sign_on_ISODate”},
current_week: {$week: “$time_stamp_sign_on_ISODate” },
current_day: {$isoDayOfWeek: “$time_stamp_sign_on_ISODate”}
},
total_worktime_seconds: {$sum: “$work_time_seconds”},
}}, {$sort: {_id: 1}},
{$project: {total_work_time_hours_base10: {$divide: ["$total_worktime_seconds", 3600] }, total_worktime_seconds: 1,}},
{$project: {total_work_time_hours_base10_rounded: {$round: ["$total_work_time_hours_base10", 2] }, total_worktime_seconds: 1,}},

{$project: {current_day: 1, total_work_time_hours_base10_rounded: 1, total_worktime_seconds: 1,
weekday: { $and: [{ $gte: ["$_id.current_day", 1] }, { $lte: ["$_id.current_day", 5] } ] },
weekend_day: { $or: [{ $eq: ["$_id.current_day", 6] }, { $eq: ["$_id.current_day", 7] } ] }
}
}
])

Glad I could help get this sorted out for you :slight_smile:

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.