CODEWITHSUNDEEP
javamongodb
AfternoonTiger
AfternoonTiger

Reputation: 397

mongodb fetching all documents in the range of two dates

I have a mongo database containing documents in the following structure

{
    _id:5e4f078e688bb974ed1dbc21
    timestamp:"Mon Mar 06 23:54:55 EST 2017"
    formatted_date:"2017-03-06 23:54:55"
    steps:13
    step_delta:13
}

I'm finding it tricky to get all documents (i believe there is a simple query for this, i'm just mistaken) that fall in between the specific dates needed.

this is my mongo db query 

DBObject query = QueryBuilder.start().put("formatted_date").greaterThanEquals(startDate).and().put("formatted_date").lessThanEquals(endingDate).get();

Original, I was thinking it would be like the following sql query

String query = new StringBuilder("SELECT * FROM ").append(ACTIVITY_TABLE)
        .append(" WHERE formatted_date BETWEEN ").append(startDate)
        .append(" AND ").append(endDate).toString();

how do I make such a query in mongodb in java

Upvotes: 2

Views: 666

Answers (2)

ambianBeing
ambianBeing

Reputation: 3529

You could create a range on the field formatted_date with this:

query = new BasicDBObject(
  "formatted_date",
  new BasicDBObject("$gte", startDate).append("$lte", endingDate)
);
cursor = collection.find(query);

try {
  while (cursor.hasNext()) {
    System.out.println(cursor.next());
  }
} finally {
  cursor.close();
}

Upvotes: 1

prasad_
prasad_

Reputation: 14317

Using MongoDB Java Driver the following code queries and prints the documents between the input fromDate and toDate of formatted_date field.

MongoClient mongoClient = MongoClients.create();
MongoDatabase database = mongoClient.getDatabase("testDB");
MongoCollection<Document> collection = database.getCollection("testColl");

String fromDate = "2020-02-06";
String toDate = "2020-02-09";
MongoCursor<Document> cursor = collection
                                 .find(and(
                                           gte("formatted_date", fromDate), 
                                           lte("formatted_date", toDate)))
                                 .iterator();

cursor.forEachRemaining(System.out::println);

With the three input documents, 

{ "_id" : 1, "formatted_date" : "2020-02-06 23:54:55", "steps" : 13 }
{ "_id" : 2, "formatted_date" : "2020-02-08 10:00:00", "steps" : 10 }
{ "_id" : 3, "formatted_date" : "2020-02-10 00:30:40", "steps" : 2 }

the output is:

Document{{_id=1.0, formatted_date=2020-02-06 23:54:55, steps=13.0}}
Document{{_id=2.0, formatted_date=2020-02-08 10:00:00, steps=10.0}}

Note the collection.find​(Bson filter) method builds the query's filter using the com.mongodb.client.model.Filters factory class's gte and lte methods.

Upvotes: 2

Related Questions