Querying MongoDb for a date Range using Java

Question

I have a some data in my mongoDb collection and a field name billingDate where i am storing the billing date in ISO format as below.

{
"_id":"xxyy",
"name":"abcd",
"billingDate":ISODate("2018-01-03T13:50:05.000+0000"),
}

I want to query my collection for a date range like get all bills from one date to another date. To achieve this I get date from user and then convert it to the matching format of mongoDb.

fromTime // value from user in format("dd/mm/yyyy")
toTime  // value from user in format("dd/mm/yyyy")

DateTimeFormatter dtf = DateTimeFormatter.ofPattern("dd/MM/yyyy");
        dtf = dtf.withLocale(Locale.US);  // Locale specifies human language for translating, and cultural norms for lowercase/uppercase and abbreviations and such. Example: Locale.US or Locale.CANADA_FRENCH
        LocalDate fdate = LocalDate.parse(fromTime, dtf);
        LocalDate tdate = LocalDate.parse(toTime, dtf);
        System.out.println("date is :: "+fdate);
        LocalDateTime flocalDateTime=LocalDateTime.of(fdate, LocalTime.MIDNIGHT);
        Instant finstant=flocalDateTime.toInstant(ZoneOffset.UTC);
        LocalDateTime tlocalDateTime=LocalDateTime.of(tdate, LocalTime.MIDNIGHT);
        Instant tinstant=tlocalDateTime.toInstant(ZoneOffset.UTC);
        System.out.println("from TIme is :: "+finstant);
        System.out.println("to time is :: "+tinstant);

now when I query mongodb for data for only $gte it is working fine but when I use $lte it is giving me error.

        Date fdate=Date.from(finstant);
        Date tdate=Date.from(tinstant);
Query query= new Query();
        query.addCriteria(Criteria.where("queryBillDate").gte(fdate));
        query.addCriteria(Criteria.where("queryBillDate").lte(tdate));

         List<OPDBill> listOfBills = mongoTemplate.find(query, OPDBill.class,OPDBillsCollection);

error log is as below:

SEVERE: Servlet.service() for servlet [spring-dispatcher] in context with path [/ClipCare1.0] threw exception [Request processing failed; nested exception is org.springframework.data.mongodb.InvalidMongoDbApiUsageException: Due to limitations of the com.mongodb.BasicDBObject, you can't add a second 'queryBillDate' criteria. Query already contains '{ "queryBillDate" : { "$gte" : { "$date" : "2018-01-01T00:00:00.000Z"}}}'.] with root cause
org.springframework.data.mongodb.InvalidMongoDbApiUsageException: Due to limitations of the com.mongodb.BasicDBObject, you can't add a second 'queryBillDate' criteria. Query already contains '{ "queryBillDate" : { "$gte" : { "$date" : "2018-01-01T00:00:00.000Z"}}}'.
    at org.springframework.data.mongodb.core.query.Query.addCriteria(Query.java:99)
    at com.aventyn.hms.dao.BillingDAOImpl.findDocRevBill(BillingDAOImpl.java:369)
    at com.aventyn.hms.controller.LoginController.getDocRevenue(LoginController.java:180)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

I have update my question and I have changed my instace to date instance but now it is giving me another error.

I think you may need to convert Instant to a Date instance. — achAmháin
– achAmháin, Commented Jan 3, 2018 at 8:53
The message seems clear enough to me: “you can't add a second 'queryBillDate' criteria”. Is there a “between” condition that you may use instead of gte and lte? Asking without knowing query and criteria API. — Anonymous
– Anonymous, Commented Jan 3, 2018 at 9:45
Try adding criteria only once, like query.addCriteria(Criteria.where("queryBillDate").gte().lte()); — CrazySabbath
– CrazySabbath, Commented Jan 3, 2018 at 9:48

Nikhil Pareek · Accepted Answer · 2018-01-03 11:36:08Z

5

I see you are using Spring. Try using MongoOperations:

Date fdate=Date.from(finstant);
Date tdate=Date.from(tinstant);

List<OPDBill> listOfBills = mongoOperations.find(
                Query.query(Criteria.where("queryBillDate").gte(fDate).lt(tDate)), 
                OPDBill.class,
                OPDBillsCollection);

answered Jan 3, 2018 at 11:36

Nikhil Pareek

7642 gold badges9 silver badges25 bronze badges

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

Collectives™ on Stack Overflow

Querying MongoDb for a date Range using Java

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related