date comparison in JPQL-CodePudding

I have a comparison of two dates in my application code. The code is in Flutter, but is easy, so you can understand it even if you don´t know Dart:

selectedDayCalendar.isAfter(item.canceledDate.add(Duration(days: 15)))

Here I want to know when the date selected in Calendar is after other date 15 days. The problem is: data can only be shown when date 1 is before or on the same day as date 2 ( 15 days). If I do this validation in the application, the API will still bring data that I will not use, so I wanted to do this validation in the API itself. The API can only bring in data where:

!selectedDayCalendar.isAfter(item.canceledDate.add(Duration(days: 15)))

How I can do this validation in JPQL Query SELECT?

CodePudding user response：

JPQL doesn't support adding days to a date sadly, so you'll have to use a native query:

SELECT e FROM Item e WHERE DATEADD(DAY, 15, e.canceledDate) < CURRENT_DATE

I think something like that.

But some people don't like the mix of using native and JPQL queries combined.

CodePudding user response：

RESOLVED:

I put Instant.now() in a local variable, then I created another one with instant.now() 15 days. I passed these two variables by parameter to the function JPQL Query.

it was like this:

        Instant startDate = Instant.now();
        Instant endDate = startDate.plus(15, ChronoUnit.DAYS);

        return eventService.findByTrainerLoginCanceledDateBetween(startDate, endDate);

and JPQL:

@Query(
        "SELECT e "  
        "FROM Event e "  
        "WHERE e.status = '' OR e.canceledDate BETWEEN :startDate AND :endDate"
    )
    List<Event> findEventByLoginAndCanceledDateBetween(Instant startDate, Instant endDate);