Match time only in mongodb aggregation

user_8174001 0
Viewed 313

I am storing opening times as an array of shifts every day like below:

{
  Monday: [{
    startTime: {
      hour: 8, 
      minute: 50
    },
    endTime: {
      hour: 20, 
      minute: 30
    }
  }];
}

enter image description here

I am trying to retrieve documents between the start and end of a shift, using MongoDB aggregate $match operator like below:

{
  $match: {
    'Monday.startTime.hour': {              // ex: 8
      $lte: parseInt(now.format('HH'), 10), // now hours: 18
    },
    'Monday.startTime.minute': {            // ex: 50
      $lte: parseInt(now.format('mm'), 10), // now minutes: 40
    },
    'Monday.endTime.hour': {                // ex: 20
      $gte: parseInt(now.format('HH'), 10), // now hours: 18
    },
    'Monday.endTime.minute': {              // ex: 30
      $gte: parseInt(now.format('mm'), 10), // now minutes: 40
    },
  }
}

BUT the problem is that we have a shift example shown in the image below, the first match condition:

'Monday.startTime.hour': {
  $lte: parseInt(now.format('HH'), 10),
}

will pass as 8 is smaller than 18.

but the second match condition which matches the minutes part:

'Monday.startTime.minute': {            // ex: 50
  $lte: parseInt(now.format('mm'), 10), // now minutes: 40
},

will fail as 50 is greater than 40

although in real life 08:50 comes before 18:40

mongoose node.js mongodb datetime javascript
2 Answers

I was able to solve the issue by comparing the hours first by using $and and $or operators see explained code below:

{
  $and: [ // grouping two conditions that now time needs to be between the start and the end of the shift.
    {
      $or: [ // compare the start time hour of the shift first 
        {
          'Monday.startTime.hour': {
            $lt: parseInt(now.format('HH'), 10),
          },
        },
        {
          $and: [ // if the upper condition didn't work will need to compare hours and minutes 
            {
              'Monday.startTime.hour': {
                $lte: parseInt(now.format('HH'), 10),
              },
            },
            {
              'Monday.startTime.minute': {
                $lte: parseInt(now.format('mm'), 10),
              },
            },
          ],
        },
      ],
    },
    {
      $or: [ // compare the end time hour of the shift first 
        {
          'Monday.endTime.hour': {
            $gt: parseInt(now.format('HH'), 10),
          },
        },
        {
          $and: [ // if the upper condition didn't work will need to compare hours and minutes
            {
              'Monday.endTime.hour': {
                $gte: parseInt(now.format('HH'), 10),
              },
            },
            {
              'Monday.endTime.minute': {
                $gte: parseInt(now.format('mm'), 10),
              },
            },
          ],
        },
      ],
    },
  ];
}
user_81740010

Change Everything to Minutes. That will help you to solve this problem.

8:50 should be 530 and 20:30 should be 1230.

Current time will be 1120(18:40). So by changing to minutes you can able to solve this problem.

user_44889880
Related