How to compare dates in Mongo query

Question

I am trying to get the Mongo database to return all of the records created within the past week. Each record has a field 'created at' which will be something like '6/22/17 09:14'. How do I check if this date occurred in the past week?

I have some code that looks like this:

the_collection.find({ALERT_CREATED : {&gt : new Date() - ONE_WEEK}}).toArray(function(error, results) {
    if (error) {
        callback(error);
    } else {
        callback(null, results);
    }
});

With the different date formats, the two dates can't be compared.

UPDATE:
To clarify the issue, the dates in the database have the form 6/22/17 09:14 and as such cannot be compared correctly against a Date() object. Is there any way to compare them correctly?

I would strongly suggest storing dates as Date type, so you can perform date comparisons with them. — Chava Geldzahler
– Chava Geldzahler, Commented Jun 22, 2017 at 23:44

Toan Tran · Accepted Answer · 2017-06-22 22:15:21Z

2

You may need to subtract days in MongoDB query. Please try below code:

   the_collection.find(
   {
       "ALERT_CREATED": 
       {
           $gte: (new Date((new Date()).getTime() - (7 * 24 * 60 * 60 * 1000)))
       }
   })

   1 day = 1*24*60*60000 = 1 x 24 hours x 60 minutes x 60 seconds x 1000 milliseconds

edited Jun 22, 2017 at 22:15

answered Jun 22, 2017 at 22:09

Toan Tran

2,0673 gold badges25 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

sumowrestler Over a year ago

This is also in a different date format as specified. The dates will not compare correctly.

Chava Geldzahler · Accepted Answer · 2017-06-22 22:35:12Z

2

First you need the date to compare the created_at field to (today - 7), in MongoDB's date format. Then get only dates that come after.

var lastWeek = new Date();
lastWeek.setDate(lastWeek.getDate() -7);

db.the_collection.find({'created_at': {'$gte': lastWeek}});

edited Jun 22, 2017 at 22:35

answered Jun 22, 2017 at 22:12

Chava Geldzahler

3,7501 gold badge20 silver badges32 bronze badges

1 Comment

sumowrestler Over a year ago

This does not work. 6/22/17 15:44 > Thu Jun 15 2017 15:44:04 GMT-0700 (Pacific Standard Time) returns false. Additionally, this does not fix the problem; the dates are still in different formats.

Manuel · Accepted Answer · 2018-09-22 09:36:12Z

0

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>
    }
}

answered Sep 22, 2018 at 9:36

Manuel

15.2k7 gold badges66 silver badges153 bronze badges

Collectives™ on Stack Overflow

How to compare dates in Mongo query

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related