Filter date with $lte and $gte not working

Working with Data Drivers
3

Hi @MAY_CHEAPER,

There appears to be some formatting differences in the date values you’ve provided in the match stage compared to that noted in the Date() documentation.

I believe the way you have it formatted would:

specify the datetime in the client’s local timezone and returns the ISODate with the specified datetime in UTC.

I note this as MongoDB stores times in UTC, and will convert any local time representations into this form.

In saying so, can you advise what your expected output is? I ran the same $match stage on a test environment and was able to retrieve the same document you provided in the post back.

Why can’t I get dayoffs with from and to within a day 2022/01/03?

If you’re specifically wanting on elements inside of the dayoffs array field that match the date range, perhaps use of the $filter operator may work for you.

Please see a simple example below. I have only tested this on a single document but you can see the additional field filteredEntries contains only entries that match the date range:

db.test.aggregate(
{
  '$addFields': {
    filteredEntries: {
      '$filter': {
        input: '$dayoffs',
        as: 'entry',
        cond: {
          '$and': [
            {
              '$gte': [ '$$entry.from', ISODate("2022-01-03T00:00:00.000Z") ]
            },
            {
              '$lte': [ '$$entry.to', ISODate("2022-01-03T23:59:59.000Z") ]
            }
          ]
        }
      }
    }
  }
}

Output (with an additional $project on filteredEntries just to keep post smaller and to verify that only entries within the range were included in the filteredEntries array field):

[
  {
    _id: ObjectId("62fb04b5ab5d901302a457e5"),
    filteredEntries: [
      {
        kind: 'A',
        from: ISODate("2022-01-03T01:30:00.000Z"),
        to: ISODate("2022-01-03T10:30:00.000Z"),
        reason: 'B',
        _id: ObjectId("62e2386dda2753fad5ca65ce")
      }
    ]
  }
]

If you believe this may work for you, please test thoroughly in a test environment to verify it meets your use case and requirements.

Hope this helps.

Regards,
Jason

1 Like