MongoRepository findByCreatedAtBetween not returning accurate results

Question

My document structure in Mongo is like this :

db.user.find()

{
        "_id" : ObjectId("560fa46930a8e74be720009a"),
        "createdAt" : ISODate("2015-10-03T09:47:56.333Z"),
        "message" : "welcome",
}
{
            "_id" : ObjectId("560fa46930a8e723e720009a"),
            "createdAt" : ISODate("2015-10-03T09:48:25.048Z"),
            "message" : "thank you"
}

When I use the below query in my Mongo Shell to find documents between two given timestamps, i get the right result :

db.user.find({createdAt:{$gte:ISODate("2015-10-03T09:40:25.048Z"),$lte:ISODate("2015-10-03T09:50:56.333Z")}})

I am using MongoRepository with Spring in my REST service to work with the Java driver. Below is the user repository:

public interface UserRepository extends MongoRepository<User, String> 
{
       ArrayList<User> findbyCreatedAtBetween(Date d1, Date d2);

}

When I call make the following call in my service, it does not return any result userRepository.findbyCreatedAtBetween(2015-10-03T09:40:25.048Z, 2015-10-03T09:50:29.006Z)

However it returns the result when I give d1 as previous day : userRepository.findbyCreatedAtBetween(2015-10-02T09:40:25.048Z, 2015-10-03T09:50:29.006Z)

Any ideas on how I can resolve this? Please help!

Answer 1

Breaking it down, the query with the keyword Between is being executed against the MongoDB database with the logical result {"createdAt" : {"$gt" : d1, "$lt" : d2}} so there is a chance that you are not getting the documents that have the createdAt date inclusive within the given date range i.e. d1 < createdAt < d2 since the given date range does not satisfy the criteria. For reference these are some of the interpretations on the query methods:

Supported keywords for query methods

Keyword     Sample                              Logical result
After       findByBirthdateAfter(Date date)     {"birthdate" : {"$gt" : date}}
Before      findByBirthdateBefore(Date date)    {"birthdate" : {"$lt" : date}}
Between     findByAgeBetween(int from, int to)  {"age" : {"$gt" : from, "$lt" : to}}

As a workaround, you may want to use the @Query annotation. I haven't tested this but you may want to try the following custom query implementation example:

public interface UserRepository extends MongoRepository<User, String>  {
    @Query(value = "{ 'createdAt' : {$gte : ?0, $lte: ?1 }}")
    public ArrayList<User> findbyCreatedAtBetween(Date from, Date to);
}

If the above doesn't work for you, create a custom interface and your implementation class to execute the custom query. For example, create an interface with a name that appends Custom:

public interface UserRepositoryCustom {
    public List<User> findbyCreatedAtBetween(Date from, Date to); 
}

Modify the UserRepository and add the UserRepositoryCustom interface to be extended:

@Repository
public interface UserRepository extends UserRepositoryCustom, MongoRepository {

}

Create your implementation class to implement the methods defined in UserRepositoryCustom interface.

public class UserRepositoryImpl implements UserRepositoryCustom {

    @Autowired
    MongoTemplate mongoTemplate;

    @Override
    public ArrayList<User> findbyCreatedAtBetween(Date from, Date to) {
        return mongoTemplate.find(
            Query.addCriteria(Criteria.where("createdAt").gte(from).lte(to));
    }
}