CODEWITHSUNDEEP
mongodbmongoosemongodb-queryaggregation-framework
silencedogood
silencedogood

Reputation: 3299

Querying date range in aggregate query returns nothing or ignores dates

In my aggregate query I'm trying to add conditions in the $match statement to return only records within given date range. Without converting to ISOString, I get a set of records that ignores the date range completely. When I convert to ISOString, I get nothing (returns empty set). I've tried using the $and operator, still nothing.

I've tried all the solutions on stack to no avail. Here's my code:

$match: { 
            $and: [
                {'author.id': { $ne: req.user._id }},
                {'blurtDate': { $gte: test1.toISOString() }},
                {'blurtDate': { $lte: test2.toISOString() }}
            ]
        }

test1 and test2 are correct, I checked them on console log they reflect as follows:

2019-06-02T12:44:39.000Z -- 2019-07-02T12:44:39.928Z

I also tried without the $and operator like so:

$match: { 
            'author.id': { $ne: req.user._id },
            'blurtDate': { $gte: test1.toISOString() },
            'blurtDate': { $lte: test2.toISOString() } 
        }

Which again returns nothing. Any help much appreciated!

EDIT: Wanted to emphasize that test1 and test2 are new date objects:

test1 = new Date(qryDateFrom);  //Tried .toISOString() as well
test2 = new Date(qryDateTo);

Without .toISOString(), I get a return of values that ignores the dates. With .toISOString I get an empty return.

Here's an example document that should be returned:

{
    "_id" : ObjectId("5d0a807345c85d00ac4b7217"),
    "text" : "<p>Seriously RV style.</p>",
    "blurtDate" : ISODate("2019-06-19T18:35:31.156Z"),
    "blurtImg" : "04643410-92c1-11e9-80b6-a3262311afff.png",
    "vote" : 0,
    "author" : {
        "id" : ObjectId("5cb5df0ef7a3570bb4ac6e05"),
        "name" : "Benjamin Paine"
    },
    "__v" : 0
}

When I remove .toISOString(), I get documents outside of the expected date range, such as this one in May (query should only return between june 2 and july 2).

{
    "_id" : ObjectId("5d07ebaf9a035117e4546349"),
    "text" : "<p>A start to something more...</p>",
    "blurtDate" : ISODate("2019-05-15T19:36:15.737Z"),
    "blurtImg" : "2be7a160-9137-11e9-933f-6966b2e503c7.png",
    "vote" : 0,
    "author" : {
        "id" : ObjectId("5cb5df0ef7a3570bb4ac6e05"),
        "name" : "Benjamin Paine"
    },
    "__v" : 0
}

Upvotes: 1

Views: 222

Answers (1)

JohnnyHK
JohnnyHK

Reputation: 311835

Your docs contain actual Date objects, so remove the .toISOString()s from your query. But you'll also need to combine your $gte and $lte terms into a single object:

$match: { 
    'author.id': { $ne: req.user._id },
    'blurtDate': { $gte: test1, $lte: test2 }
}

Upvotes: 1

Related Questions