Solution $lte and $gte Dates in aggregates

[ChrisBrech]

I was having trouble finding a solution for comparing dates in aggregates for Moralis. It seems to give incorrect results when using $lte and $gte. Code that works in regular MongoDB breaks for Moralis. I did come up with a solution, included below, for those that were also banging their head against a wall.

For example:
I have two Date columns “start” and “end”.
I have let now = new Date();
let pipeline = [ { match : { $expr : { $lte: [ "$start", now ] } } } ];
where half of documents have a start less than now,
returns 0 results.
And
let pipeline = [ { match : { $expr : { $gte: [ "$end", now ] } } } ];
where half of documents have an end greater than now,
returns all results (including incorrect ones).

The solution:

let now = new Date().getTime();
let pipeline = [ { match : { $expr : { $lte: [ { $toLong: "$start" }, now ] } } } ];

or

let pipeline = [ { match : { $expr : { $gte: [ { $toLong: "$end" }, now ] } } } ];

Or, in my specific case where I want to check that today is between the start and end dates:

let pipeline = [ {
    match : {
      $expr : {
        $and: [
            { $lte: [ { $toLong: "$start" }, now ] },
            { $gte: [ { $toLong: "$end" }, now ] }
        ]
} } } ];

In conclusion, the trick is to get the current date as a timestamp (using getTime()), and do the same with the Date column (using $toLong), and compare that with $lte and $gte instead.

I hope this helps someone else having trouble with Dates in aggregates.

P.S.: In case someone ends up here having trouble comparing to createdAt or updatedAt, that wasn’t my issue here but I see posts saying to try created_at or updated_at.

P.P.S.: If this has been solved elsewhere on this forum, my apologies. I couldn’t find it.

[cryptokid]

Hello, It could also depend on how the dates are saved in the database, there are at least 3 possible date formats that mongo db supports.