Reputation: 1916

Spring Data MongoDB Date Between

I use spring data mongodb.

I want the records between two dates. The following MongoDB Query works:

db.posts.find({startDate: {$gte: start, $lt: end}});

My attempted Spring data query object code translation does not work:

Query query = new Query();
query.addCriteria(Criteria.where("startDate").gte(startDate)
                            .and("startDate").lt(endDate));

What is the correct order of method calls to build the Mongo query I need?

Upvotes: 57

Answers (13)

Balraj

Reputation: 41

Another approach to this could be create a DateUtility class to get LocalDateTime from input LocalDate:

public class DateUtility {
    public static LocalDateTime toStartOfDay(LocalDate date) {
        return Optional.ofNullable(date).map(LocalDate::atStartOfDay).orElse(null);
    }

    public static LocalDateTime toEndOfDay(LocalDate date) {
        return Optional.ofNullable(date).map((localDate) ->
                localDate.atTime(LocalTime.MAX)).orElse(null);
    }
}

Then use it in the query criteria:

Query query = new Query();  query.addCriteria(Criteria.where("startDate").gt(CommonUtils.toStartOfDay(startDate)).lt(CommonUtils.toEndOfDay(startDate)));

This query will give result from whole day for a given date.

Upvotes: 0

DoanTuyet

Reputation: 1

Can you try this below query. This should work for your case.

Query query = new Query(
        Criteria.andOperator(
            Criteria.where("startDate").gte(startDate),
            Criteria.where("startDate").lt(endDate)
        )
    );

Upvotes: 0

user3664081

Reputation: 1

Search By Date Column in spring boot mongo db using criteria(Complex Query)[Search by date column in spring boot mongodb using criteria][1]

Upvotes: -1

JianrongChen

Reputation: 206

Use MongoRepository Query method like this:

List<T> findByStartDateBetween(Range<Integer> dataRange);

use Range for detail range settings.

simliar question

Upvotes: 3

SVK PETO

Reputation: 124

public interface MyRepository extends MongoRepository<MyObject, MyObjectId> {
    List<MyObject> findByMydatefieldBetween(DateTime startDate, DateTime endDate);
}

https://docs.spring.io/spring-data/mongodb/docs/2.2.x-SNAPSHOT/reference/html/#mongodb.repositories.queries

Upvotes: 3

Sandun Susantha

Reputation: 1140

if (metaData.getToValue() == null){
dynamicCriteria = Criteria.where("metaData.key").is(metaData.getKey()).andOperator(Criteria.where("metaData.value").is(metaData.getFromValue()));
}else {
dynamicCriteria = Criteria.where("metaData.key").is(metaData.getKey()).orOperator(Criteria.where("metaData.value").gte(metaData.getFromValue()).lt(metaData.getToValue()));
}

Upvotes: 2

Pradeep Sreeram

Reputation: 388

I did like this

public interface PolicyRepository extends MongoRepository<Policy, String> {
    @Query("{'lastDate':{$gt:?0,$lt:?1}}")
        public List<Policy> findAllPolicies(Date today,Date somedate);
}

Upvotes: 5

S.Daineko

Reputation: 1968

You also can add query annotaion:

@Query("{'date' : { $gte: ?0, $lte: ?1 } }")                 
public List<AnyYourObj> getObjectByDate(Date from, Date to);

Or proper spring data method signature:

public List<AnyYourObj> findByDateBetween(Date from, Date to);

Both of these approaches give the same result. You can read more here https://www.baeldung.com/queries-in-spring-data-mongodb and here https://docs.spring.io/spring-data/mongodb/docs/current/reference/html/

Upvotes: 19

DarwinFernandez

Reputation: 364

Reference here

Query query = new Query(
  Criteria.where("ip").is(ip)
  .andOperator(
    Criteria.where("createdDate").lt(endDate),
    Criteria.where("createdDate").gte(startDate)
  )
);

Upvotes: 12

James Jithin

Reputation: 10555

I had to find dates between on field publishedDate and here is how I did it:

    Criteria publishedDateCriteria = Criteria
                        .where("publishedDateObject").gte(psDate)
                        .lte(peDate);
    Query query = new Query(publishedDateCriteria);
    mongoTemplate.find(query,
                        MyDocumentObject.class));

Upvotes: 7

user2735611

Reputation: 41

It works, he is some sample code:

Criteria criteria = Criteria.where("pt").gte(startDate)
       .andOperator(Criteria.where("pt").lt(endDate));

Upvotes: 4

Yohan Liyanage

Reputation: 7000

Do not include the 'and("startDate")' part in your criteria.

Instead of :

query.addCriteria(Criteria.where("startDate").gte(startDate).and("startDate").lt(endDate));

You should use:

query.addCriteria(Criteria.where("startDate").gte(startDate).lt(endDate));

When you include the 'and("startDate")' part, Mongo see's it as two different entries on the same property.

Upvotes: 118

user1367351

Reputation: 102

This works on version 2.7.2 of the Java driver

DBCollection coll = db.getCollection("posts");  

BasicDBObject date = new BasicDBObject();
date.append("$gte", new Date(startDate));
date.append("$lte", new Date(endDate));

DBObject query = new BasicDBObject();
query.put("date", date);

DBCursor cursor = coll.find(query);

Also, for the record you have "startDate" for both the gte and the lte parameters.

Upvotes: 4

Spring Data MongoDB Date Between

Answers (13)

Related Questions