Filter date with $lte and $gte not working

[MAY_CHEAPER]

I have a document like this:

{
    "_id": ObjectId(),
    "dayoffs" : [
		{
			"kind" : "A",
			"from" : ISODate("2022-01-01T03:00:00Z"),
			"to" : ISODate("2022-01-01T03:00:00Z"),
			"reason" : "B",
			"_id" : ObjectId("62d66bc24c17ee18272f97ad"),
			"approvers" : [ ]
		},
		{
			"kind" : "A",
			"from" : ISODate("2022-01-01T03:00:00Z"),
			"to" : ISODate("2022-01-01T03:00:00Z"),
			"reason" : "B",
			"_id" : ObjectId("62d66be73844cc213cf958cf"),
			"approvers" : [ ]
		},
		{
			"kind" : "A",
			"from" : ISODate("2022-01-02T01:30:00Z"),
			"to" : ISODate("2022-01-02T10:30:00Z"),
			"reason" : "B",
			"_id" : ObjectId("62e238306821bfc1ee2bd308")
		},
		{
			"kind" : "A",
			"from" : ISODate("2022-01-03T01:30:00Z"),
			"to" : ISODate("2022-01-03T10:30:00Z"),
			"reason" : "B",
			"_id" : ObjectId("62e2386dda2753fad5ca65ce")
		}
	]
}

I try to query like this:

{
    "$match": {
        "dayoffs.from": {
          "$gte": new Date("2022/01/03")
        },
        "dayoffs.to": {
          "$lte": new Date("2022/01/03 23:59:00")
        }
    }
}

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

[Jason_Tran]

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