2

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.

mr.tarsa
  • 6,386
  • 3
  • 25
  • 42
Kathire
  • 53
  • 1
  • 8

1 Answers1

3

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.

Community
  • 1
  • 1
mr.tarsa
  • 6,386
  • 3
  • 25
  • 42
  • Basically i want to fetch the records less than the given date. Please let me know the query for the above – Kathire Aug 03 '16 at 12:21
  • @Kathire Updated the answer – mr.tarsa Aug 03 '16 at 12:23
  • Thanks a lot for the answer. posting another question related to mongo api query. please kindly help if possible – Kathire Aug 03 '16 at 12:34
  • Hi, Sample Collection: Employee Record: { "_id" : ObjectId("57a1bfc103c8851a98dba3b2"), "createdOn" : "1470218177561", "name" : "Raja", "description" : "Employee Raja" } Below Query is not working Calendar cal = Calendar.getInstance(); cal.add(Calendar.DATE, -5); Date dateBefore = cal.getTime(); collection.find("{createdOn: {$gte : #}}", dateBefore).as(type); Basically i am trying to find records for the last 5 days. Thanks. – Kathire Aug 03 '16 at 12:40
  • yes, but it is not allowing me to create till 90 minutes. so could you please help me or can u post for me? thanks in advance. – Kathire Aug 03 '16 at 12:52
  • @Kathire Updated the answer – mr.tarsa Aug 04 '16 at 06:02
  • This is perfectly working in mongo shell where as not working via MongoCollection API via jongo. throws cannot parse exception on new - { createdOn: { $gt: new Date(new Date()).getTime() - 7 * 24 * 60 * 60 * 1000 } } – Kathire Aug 04 '16 at 06:30
  • `code` java.lang.IllegalArgumentException: Cannot parse query: { createdOn: { $gt: new Date(new Date()).getTime() - 7 * 24 * 60 * 60 * 1000 } } at org.jongo.query.BsonQueryFactory.createQuery(BsonQueryFactory.java:162) at org.jongo.Find.(Find.java:47) at org.jongo.MongoCollection.find(MongoCollection.java:88) at org.jongo.MongoCollection.find(MongoCollection.java:84) `code` – Kathire Aug 04 '16 at 06:48
  • @Kathire Updated the answer. Next time try to specify all aspects of your problem and the environment where you want to handle it (f.e that you need the result in Jongo, not just in mongo Shell). – mr.tarsa Aug 04 '16 at 07:27