MongoRepository @Query Failed to parse string as a date

Question

First of all, my issue is searching Collections in MongoDB via Spring MongoDb`s MongoRepository.

My Object:

{
    "_id" : ObjectId("5c78e1f447f39c2eacb229d7"),
    "lab" : "xxx",
    "type" : "Holiday",
    "description" : "Lunar New Year",
    "start_date" : ISODate("2019-02-04T02:37:42.152Z"),
    "end_date" : ISODate("2019-02-08T06:37:42.152Z"),
    "all_day" : true,
    "_class" : "xxx.Event"
}

i can do as my wish in Mongo query as:

db.getCollection('event').find({"start_date" : {$gte :ISODate( "2019-02-03T02:37:42.152Z") , $lte :ISODate( "2019-02-08T02:37:42.152Z")}})

(you can replace ISODate with new Date)

But to do it in Spring, i want to do it as:

@Query("   $or: [ {start_date : {$gte :ISODate( ?0 ) , $lte :ISODate( ?1)}} , {end_date : {$gte :ISODate( ?0) , $lte :ISODate( ?1)}} ]  }  ")
List<Event> findAllEventByTime(String from, String to);

But it fail, i searched in two topic: here and there

and end up with

@Query("{ 'start_date' : {$gte : {'$date': '?0'}, $lte :{'$date': '?1'} }}")
List<Event> findAllEventByTime(String from, String to);

But once again, i had the problem with parsing:

2019-03-22 10:09:48.261 ERROR 9316 --- [ XNIO-2 task-1] o.z.problem.spring.common.AdviceTrait : Internal Server Error

org.bson.json.JsonParseException: Failed to parse string as a date at org.bson.json.JsonReader.visitDateTimeExtendedJson(JsonReader.java:1057)

I try with recomment:

Try param: Fri Mar 22 10:09:48 ICT 2019 and 2019-03-22T03:09:48.227Z and 2016-04-14 00:00:00

All of this going down... Can you guys help me to fix it?

Work-Flow: Params from FE (String) ~> Go to BE ~> Call Repo as above

score 1 · Answer 1 · answered Mar 22 '19 at 07:07

1

You can make spring data jpa method for the same like below:-

List<Event>  findByStart_dateIsAfterAndEnd_dateIsBefore(Date startDate, Date endDate);

answered Mar 22 '19 at 07:07

Nishant Bhardwaz

924
8
21

I do try: List findAllBystartDate(Instant startdate); and get : 2019-03-22 11:22:15.059 ERROR 7740 --- [ XNIO-2 task-3] o.z.problem.spring.common.AdviceTrait : Internal Server Error org.bson.codecs.configuration.CodecConfigurationException: Can't find a codec for class java.time.ZonedDateTime. – Nam Nguyễn Mar 22 '19 at 07:32
thanks for your suggestion, i test all the way and end up using auto convert from Srping Data JPA, but we need to change the type – Nam Nguyễn May 02 '19 at 03:09

score 0 · Accepted Answer · answered May 02 '19 at 03:05

I resolve it with other method:

In repository
- I can't convert with raw query or any query that need to convert from String to Date
- Use the auto generate query support by MongoRepository

Page<Event> findAllByStartDateBetweenOrEndDateBetween(Instant fromDate1, Instant toDate1, Instant fromDate2, Instant toDate2, Pageable pageable);
    List<Event> findAllByStartDateBetweenOrEndDateBetween(Instant fromDate1, Instant toDate1, Instant fromDate2, Instant toDate2);

Input Data: Use Instant or LocalDate / LocalDateTime and convert it to Instant ~> Then use as param in query (auto convert by Spring)

@RequestParam Instant startDate, @RequestParam Instant endDate

and use:

eventRepository.findAllByStartDateBetweenOrEndDateBetween(startDate, endDate, startDate, endDate))

MongoRepository @Query Failed to parse string as a date

2 Answers2

Linked