CODEWITHSUNDEEP
javamongodbdate-rangedate
Vipul Singh
Vipul Singh

Reputation: 393

Querying MongoDb for a date Range using Java

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.

Upvotes: 4

Views: 8001

Answers (1)

Nikhil Pareek
Nikhil Pareek

Reputation: 764

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);

Upvotes: 5

Related Questions