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!

as @chridam mentioned the `Between` keyword doesn't include the parameters themselves in the search criteria. Apart from this you might want to replace `findbyCreatedAtBetween` by `findByCreatedAtBetween` — Vikas Prasad, Mar 30 '17 at 15:38

score 13 · Accepted Answer · answered Nov 03 '15 at 09:16

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

I had tried the first option you suggested, but that did not work. Looks like MongoRepostory is quiet limited, it is so much better to use the Repository with MongoTemplate. Thank you for your suggestion! — GareGarble, Nov 04 '15 at 08:18
@sindhunk Indeed, it's quite limited but I'm glad you found a workaround :) — chridam, Nov 04 '15 at 08:19

MongoRepository findByCreatedAtBetween not returning accurate results

1 Answers1

Linked