How to Compare Dates in Mongo Query

How can compare Date on mongo without time?

You need to match type of input with type of date field in document, either both should be Date's or strings. I would highly suggest maintain dates as dates in DB. Also you need to know that dates in MongoDB are of format ISODate() and holds UTC date.

1. If your DB date field is of type date :

I want to filter records of 22-May or earlier

As you wanted to get documents <= 22-May, then sending new Date('2020-05-22') doesn't work. Cause :

when you do new Date('2020-05-22'), it will give you Fri May 22 2020 00:00:00 GMT only if you belong to UTC, for example if you're in New York America which is 4 hours behind UTC then it would result in Thu May 21 2020 20:00:00 GMT-0400 (Eastern Daylight Time) which represents EDT, basically it's your system/app server time i.e; local date time.

So if your region is behind UTC then you'll get a back date Thu May 21 2020 otherwise if it's ahead of UTC then there is no issue you'll see Fri May 22 2020.

Ok, now that we've fixed date issues, but we need to look into hours now :

Since you want docs <= 22-May then Fri May 22 2020 00:00:00 GMT doesn't work you need to have either <= Fri May 22 2020 23:59:59 GMT or Sat May 23 2020 00:00:00 GMT. In order to get that :

let date = new Date('2020-05-22')
date.setDate(date.getUTCDate()); // Setting utc date, Only useful if you're region is behind UTC
date = new Date(date.setHours(23,59,59,999)) // This overrides hours generated with 23:59:59 - which is what exactly needed here.

/** Now do your query */

{ date: { $lte: date }}

2. If your DB date field is of type string :

Then you don't need to convert string to date, instead you can send input date in string format :

let date = new Date('2020-05-22').toISOString() // 2020-05-22T00:00:00.000Z
/** Above would get you an ISO string no matter which region you're in, 
  * now since we need `2020-05-22T23:59:59.000Z` which is not easy on ISO string
  * We would just do +1 on date like `new Date('2020-05-23').toISOString()` - // 2020-05-23T00:00:00.000Z */

let date = new Date('2020-05-23').toISOString(); // like this
date = date.slice(0, -1) // removing `Z` from date string as your `date` field doesn't have this.
// Now your query is just `$lt`
{ date: { $lt: date }}

Test : mongoplayground

How to compare dates in Mongo query

The date does not seem to be a standardized format. You would have to manually convert the given string into a valid date string.

One way is splitting up your string and using dateFormParts as documented here to construct a date.

{
    $dateFromParts : {
        'year': <year>, 'month': <month>, 'day': <day>,
        'hour': <hour>, 'minute': <minute>, 'second': <second>,
        'milliseconds': <ms>, 'timezone': <tzExpression>
    }
}

How to compare two dates with Mongodb (find query using NestJS)

    let firstPoint = new Date();
    firstPoint.setHours(0);
    firstPoint.setMinutes(0);
    firstPoint.setSeconds(0);
    let lastPoint = new Date();
    lastPoint.setHours(23);
    lastPoint.setMinutes(59);
    lastPoint.setSeconds(59);

    const posts = await this.mbRepository.find({ publishDate: { $gte: firstPoint, $lt: lastPoint } } });

Get current day and make two point. One as begining of the day and another as end of the day. Then you can use mongodb operator $gte and $lt. In this way you will get all post which have been publish in this time range.

Mongodb shell aggregate query comparing dates not returning results

Issue with your query is when you're doing :

{$match:{CreatedAt:{$gte:"$DateFloor"}}}

You're actually checking for documents where CreatedAt field's value to be greater than input string value "$DateFloor", So $match is not considering "$DateFloor" as another field in the same document rather it's considering it as a string value. So to compare two fields from same document you need to use $gte with $expr (which will let you use aggregation expressions within the query language).

{
  $match: {
    {
      $expr: {
        $gte: ["$CreatedAt", "$DateFloor"];
      }
    }
  }
}

So you might get confused when I say aggregation expressions & why $gte needs to be wrapped inside $expr - In your actual query $gte refers to comparison operator in MongoDB but in this above query $gte refers to aggregation pipeline operator where both technically does the same but which is what needed to compare two fields from same document.

comparing dates in mongodb

new Date(year, month, date) variant will have the date in current system time zone.

The reason you comparison doesn't return any results is when you pass the date in your local system zone , the meteor does a conversion from local datetime to UTC datetime as Mongo DB datetimes are in UTC time. So your input date changes from Sat Nov 19 2016 00:00:00 GMT+1100 (AEDT) to Fri Nov 18, 2016 01:00:00 UTC.

Considering user inputs date as UTC. You'll just need to parse the date explicitly as UTC.

Try

new Date(Date.UTC(2016, 11, 19, 0, 0, 0, 0)

and pass this to the query.

Compare 2 dates in mongo find method

For MongoDB 3.6 and newer:

The $expr operator allows the use of aggregation expressions within the query language, thus you can leverage the use of $dateToString operator to transform the date field:

db.test.find({ 
    "$expr": { 
        "$ne": [ 
             { "$dateToString": { "format": "%Y-%m-%d", "date": "$created" } }, 
             { "$dateToString": { "format": "%Y-%m-%d", "date": "$last_active" } }
        ] 
    } 
})

or using aggregation framework with $match pipeline

db.test.aggregate([
    { "$match": { 
        "$expr": { 
            "$ne": [ 
                { "$dateToString": { "format": "%Y-%m-%d", "date": "$created" } }, 
                { "$dateToString": { "format": "%Y-%m-%d", "date": "$last_active" } }
            ] 
        } 
    } }
])

---

For MongoDB 3.0+:

You can also use the aggregation framework with the $redact pipeline operator that allows you to process the logical condition with the $cond operator and uses the special operations $$KEEP to "keep" the document where the logical condition is true or $$PRUNE to "remove" the document where the condition was false.

Consider running the following aggregate operation which demonstrates the above concept:

db.test.aggregate([
    {
        "$redact": {
            "$cond": [
                { 
                    "$ne": [ 
                        { "$dateToString": { "format": "%Y-%m-%d", "date": "$created" } }, 
                        { "$dateToString": { "format": "%Y-%m-%d", "date": "$last_active" } }
                    ] 
                },
                "$$KEEP",
                "$$PRUNE"
            ]
        }
    }
])

This operation is similar to having a $project pipeline that selects the fields in the collection and creates a new field that holds the result from the logical condition query and then a subsequent $match, except that $redact uses a single pipeline stage which is more efficient:

db.test.aggregate([
    {
        "$project": { 
            "created": 1, 
            "last_active": 1,
            "sameDay": { 
                "$cond": [ 
                    { 
                        "$eq": [ 
                            {"$substr" : ["$last_active",0, 10]}, 
                            {"$substr" : ["$created",0, 10]}
                        ] 
                    }, true, false 
                ]
            } 
        } 
    },
    { "$match": { "sameDay": false } }
])

0r

db.test.aggregate([
    {
        "$project": { 
            "created": 1, 
            "last_active": 1,
            "sameDay": { 
                "$cond": [ 
                    { 
                        "$eq": [ 
                            { "$dateToString": { "format": "%Y-%m-%d", "date": "$created" } }, 
                            { "$dateToString": { "format": "%Y-%m-%d", "date": "$last_active" } }
                        ] 
                    }, true, false 
                ]
            } 
        } 
    },
    { "$match": { "sameDay": false } }
])

---

Another approach would be to use the $where operator in your find() method but note that the query will be fairly slow since using $where alone requires a table scan and the database executes the JavaScript expression or function for each document in the collection, so combine with indexed queries if you can as query performance also improves when you express it using the standard MongoDB operators (e.g., $gt, $in):

db.test.find({ 
   "$where": function() { 
       return this.created.getDate() !== this.last_active.getDate() 
   } 
});

or more compact:

db.test.find({ "$where": "this.created.getDate() !== this.last_active.getDate()" });

---

With the input:

/* 0 */
{
    "_id" : 1,
    "created" : ISODate("2014-12-19T06:01:17.171Z"),
    "last_active" : ISODate("2014-12-21T15:38:13.842Z")
}

/* 1 */
{
    "_id" : 2,
    "created" : ISODate("2015-07-06T12:17:32.084Z"),
    "last_active" : ISODate("2015-07-06T18:07:08.145Z")
}

/* 2 */
{
    "_id" : 3,
    "created" : ISODate("2015-07-06T06:01:17.171Z"),
    "last_active" : ISODate("2015-07-07T10:04:30.921Z")
}

/* 3 */
{
    "_id" : 4,
    "created" : ISODate("2015-07-06T06:01:17.171Z"),
    "last_active" : ISODate("2015-07-06T09:47:44.186Z")
}

/* 4 */
{
    "_id" : 5,
    "created" : ISODate("2013-12-19T06:01:17.171Z"),
    "last_active" : ISODate("2014-01-20T13:21:37.427Z")
}

The aggregation returns:

/* 0 */
{
    "result" : [ 
        {
            "_id" : 1,
            "created" : ISODate("2014-12-19T06:01:17.171Z"),
            "last_active" : ISODate("2014-12-21T15:38:13.842Z"),
            "sameDay" : false
        }, 
        {
            "_id" : 3,
            "created" : ISODate("2015-07-06T06:01:17.171Z"),
            "last_active" : ISODate("2015-07-07T10:04:30.921Z"),
            "sameDay" : false
        }, 
        {
            "_id" : 5,
            "created" : ISODate("2013-12-19T06:01:17.171Z"),
            "last_active" : ISODate("2014-01-20T13:21:37.427Z"),
            "sameDay" : false
        }
    ],
    "ok" : 1
}

---

Related Topics