mongodb filter for date comparison

Question

From a spring boot application I am querying a mongodb collection to find out active agreements on a particular day. There is no change in data and there are 31 unique agreements. When I run it for past 7 days it gives me correct count for 5 days, but for yesterday & today it give me a count of 30.Actually all the agreements in collection are valid. There are multiple documents with same agreement id - to remove duplicates I have used grouping. My code is like this:

public int getTotalActivePAsORActiveItemsByDate(LocalDate date1) {
    String collectionName = Constants.AGREEMENT_COLLECTION_NAME;
    final List<Bson> pipeline = new ArrayList<>();
    Bson q1 = match(Filters.and(Filters.eq("agreementStatus", "Active")));
    pipeline.add(q1);
    if(date1 != null) {
        Bson q2 = match(and(lte("validFromDate", date1), gte("validToDate", date1)));
        pipeline.add(q2);
    }
    String project = "{$project:{agreementId:1, " + "validFromDate:1, " + "validToDate: 1 " + "}}";
    pipeline.add(BasicDBObject.parse(project));
    String addUpStr = "{$group:{" + "_id:'$agreementId', " + "count :{'$sum':1} " + "}}";
    pipeline.add(BasicDBObject.parse(addUpStr));
    String cntStr = "{$group:{_id:null, val:{'$sum':1}}}";
    pipeline.add(BasicDBObject.parse(cntStr));
    MongoDatabase mongo = mongoTemplate.getDb();
    final AggregateIterable<Document> ret = mongo.getCollection(collectionName).aggregate(pipeline);
    int val = 0;
    try {
        val = ret.first().getInteger("val");
    } catch (final Exception e) {
        e.printStackTrace();
    }
    
    return val;
}

And a sample document:

{_id=600ae734d752d67e519a34b6, 
 agreementStatus=Active, 
 validFromDate=Wed Oct 01 05:30:00 IST 2014, 
 validToDate=Sun Mar 31 05:30:00 IST 2024, 
 agreementId=VPC_BF_NW_Hu_LU70_NSO_MULT_400002129_D57E911804B9

}

The whole problem stems from date. Is mongodb failing in date comparison?

question from:https://stackoverflow.com/questions/65857907/mongodb-filter-for-date-comparison

Answer 1

Waitting for answers