Spring Data MongoDB Date Between

Question

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?

When you put filedName (eg. startDate) more than once you get **InvalidMongoDbApiUsageException** ....you can't add a second 'startDate'... — Hamedz, Aug 17 '16 at 21:39

score 112 · Answer 1 · answered Oct 29 '12 at 16:42

112

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.

answered Oct 29 '12 at 16:42

Yohan Liyanage

6,840
3
46
63

4

That is a completely correct answer and should be marked as correct. – Fredrik Wallenius Dec 12 '12 at 12:36
3

just making a note here that "startDate" and "endDate" are java.util.Date objects and will produce a mongodb query like "startDate" : { "$gte" : { "$date" : "2014-09-01T07:00:00.000Z"} , "$lt" : { "$date" : "2014-10-01T07:00:00.000Z"}}} – java25 Oct 12 '14 at 03:59
1

query.addCriteria(Criteria.where("dob").gte(startDate).lt(endDate)); did not work in my case. What i should use to find dob object in db like : "dob" : ISODate("1991-01-22T18:30:00Z"), – Deepak Agrawal May 02 '15 at 13:15
1

@java25 so how do you fix it – JaskeyLam Jul 30 '18 at 04:03

score 16 · Answer 2 · answered Dec 24 '18 at 12:09

16

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/

answered Dec 24 '18 at 12:09

S.Daineko

1,790
1
20
29

1

how to add where condition on specific field on top of it? for example. i want AnyObject.company="flipkart" . It would be great if you can provide spring data signture for that – Sumanth Varada Jul 23 '19 at 14:43
2

@Query ("{'date' : { $gte: ?0, $lte: ?1 }, 'company': ?2 }" and method public List findByDateBetweenAndCompany(Date from, Date to, String company); – S.Daineko Jul 23 '19 at 15:28

score 12 · Answer 3 · edited Jan 30 '17 at 21:24

12

Reference here

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

edited Jan 30 '17 at 21:24

jhhoff02

1,179
1
17
24

answered Jan 30 '17 at 19:18

DarwinFernandez

344
2
6

James Jithin · Answer 4 · 2015-09-27T16:05:33.187

7

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

edited Sep 27 '15 at 16:05

answered Sep 27 '15 at 09:39

James Jithin

10,183
5
36
51

1

Thank you James, It really helpfull. – Chetan S. Choudhary Mar 27 '17 at 05:34
@user3327951, great to know. :) Thank you. – James Jithin Mar 27 '17 at 05:37

score 5 · Answer 5 · edited Feb 10 '20 at 07:10

5

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

edited Feb 10 '20 at 07:10

Karthikeyan

2,634
5
30
51

answered Aug 29 '19 at 09:29

Pradeep Sreeram

368
4
19

score 4 · Answer 6 · answered May 01 '12 at 09:14

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.

score 4 · Answer 7 · edited Jun 08 '15 at 17:29

4

It works, he is some sample code:

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

edited Jun 08 '15 at 17:29

Patrick M

10,547
9
68
101

answered Aug 31 '13 at 13:39

user2735611

41
1

I am trying something like to search for dob. But I could not find any user. Although their are 200 users in database. criteria = Criteria.where("dob").lt(startDate.getTime()).gte(endDate.getTime()); – Deepak Agrawal May 02 '15 at 12:25

score 3 · Answer 8 · edited May 07 '21 at 20:49

3

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

edited May 07 '21 at 20:49

ℛɑƒæĿᴿᴹᴿ

4,983
4
38
58

answered Aug 16 '19 at 15:12

SVK PETO

115
1
8

score 2 · Answer 9 · answered Mar 04 '20 at 11:46

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

score 1 · Answer 10 · answered Nov 17 '21 at 09:21

1

Use MongoRepository Query method like this:

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

use Range for detail range settings.

simliar question

answered Nov 17 '21 at 09:21

JianrongChen

156
1
2

score 0 · Answer 11 · edited Sep 06 '22 at 09:59

0

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

edited Sep 06 '22 at 09:59

Nishant Lakhara

2,295
4
23
46

answered Sep 05 '22 at 09:40

DoanTuyet

1

can you try to explain the answer that you attempted along with the answer? – Nishant Lakhara Sep 06 '22 at 09:56

user3664081 · Answer 12 · 2022-05-29T12:32:42.567

-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]

edited May 29 '22 at 12:32

answered May 29 '22 at 12:28

user3664081

1
1
3

Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community May 30 '22 at 04:14

Spring Data MongoDB Date Between

12 Answers12

Linked