mongodbmongodb-queryjongo

find 5 days old documents with $lt operand in MongoDB with Jongo - query returns empty result

Data in MongoDB collection has format

{ "_id" : ObjectId("57a1bfc103c8851a98dba3b2"), "createdOn": NumberLong("1470218177561"), "name": "Raja", "description": "Employee Raja" }

Mongo DB Query and Results

> new Date(1470218177561);
 ISODate("2016-08-03T09:56:17.561Z")

> new Date(1888888888888);
 ISODate("2029-11-09T03:21:28.888Z")

> db.employee.find("{createdOn: { $lt: new Date(NumberLong(1888888888888)) }}");

The last query returns no result without any errors, so I can't determine what is wrong with my query.

Basically, want to find the records for the last 5 days with `$lt operator in Jongo. Tried the next query, but it also not working

Calendar cal = Calendar.getInstance();
cal.add(Calendar.DATE, -5);
Date dateBefore = cal.getTime();
collection.find("{createdOn: {$gte : #}}", dateBefore).as(type);

Thanks.

Solution

  • You are comparing

    NumberLong("1470218177561") vs new Date(NumberLong(1888888888888))

    instead of comparing

    NumberLong("1470218177561") vs NumberLong(1888888888888)

    In your particular case the query will be

    db.employee.find( { createdOn: { $lt: NumberLong(1888888888888) } } );

    To find documents that are 5 days old you can look at.

    To get current date in milliseconds 

    > new Date(new Date()).getTime()
1470232681000

    To find 5 days old documents use $gt operator on the date that is 5 days before now

    > new Date(new Date()).getTime() - 5 * 24 * 60 * 60 * 1000
1469800939000

    So the query will be

    db.employee.find( { createdOn: { $gt: new Date(new Date()).getTime() - 5 * 24 * 60 * 60 * 1000 } } );

    To get the same result with Jongo

    Calendar cal = Calendar.getInstance();
cal.setTime(new Date());
employees.find("{ createdOn: { $gt:  # } }", cal.getTimeInMillis() - 5 * 24 * 60 * 60 * 1000);

    The problem with your query for Jongo was the same. You were trying to find by Date type, but date field of documents in your collection is NumberLong, that is millisecond long representation of Date type.

    You cannot compare 2 different types with operators like $gt.