Spring JPA get dates between a range (inclusive) in mongo with LocalDateTime

Question

Very similar to this question: Check date between two other dates spring data jpa

However, I am attempting to do this with MongoDB and java.time.LocalDateTime.

I have tried:

findAllByMetadataStartTimeBetween(start, end) (works, but exclusive start/end)
findAllByMetadataStartTimeGreaterThanEqual(start) (works, but no end)

However, when I try: findAllByMetadataStartTimeGreaterThanEqualAndMetadataStartTimeLessThanEqual(start,end)

I get the error: json can't serialize type : class java.time.LocalDateTime

Any idea why this particular combination throws this error, when the previous ones do not?

A simple `hack` is to while passing dates to the between method subtract 1 from smaller date and add 1 to the bigger date — Amit Phaltankar, Mar 05 '18 at 23:25
that's a decent idea for a workaround if I can't crack this error. thanks! — pennstatephil, Mar 05 '18 at 23:27

score 4 · Answer 1 · answered Sep 28 '20 at 07:58

Try the below code. I am sure, works for inclusive

@Query("{'dateTime' : { $gte: ?0, $lte: ?1 } }")
List<YourObject> findByDateTimeBetween(Date from, Date to);

Spring data method signature below works for exclusive

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

score 0 · Answer 2 · answered Oct 27 '19 at 23:38

You have to make custom query:

Query query = new Query(
      new Criteria().andOperator(
    Criteria.where("metadataStartTime").gte(start),
    Criteria.where("metadataStartTime").lte(end)
  )
);

return mongoTemplate.find(query, EntityName.class);

score 0 · Answer 3 · answered Aug 01 '22 at 09:51

0

JPA "Between" method is inclusive. The problem is probably with the precision of your Date Object.

Try to make sure The compared start date has the exact hours, minutes, seconds and milliseconds. Even if is the searched data is some milliseconds ahead it won't be caught by the query.

In my case, when I set them all for start date to 00:00:00.000, and for the end date to 23:59:59.999 The JPA findAllByMetadataStartTimeBetween(start, end) works fine and inclusive.

answered Aug 01 '22 at 09:51

Sami Belaid

31
1

I would not say it is inclusive :) I even fetched `seconds` from `Instant` (so the query is like `findAllByTime_SecondsBetween(from, to)` and it still does not work as expected. With subtraction and addition of one second to the period, it does. – Witold Kupś Oct 12 '22 at 06:40

Spring JPA get dates between a range (inclusive) in mongo with LocalDateTime

3 Answers3