Problems with JPA Hibernate Query with 'Interval'

user_11050643 0
Viewed 1450

I need the possibility to retrieve the orders for the past 6 months for my backend application.

 @Query("Select ord From orders ord WHERE ord.created_date  <= (current_date  - INTERVAL '6 months')")
    List<Order> findOrders();

unexpected token: INTERVAL near line 1, column 70 [Select ord From orders ord WHERE ord.created_date  <= (current_date  INTERVAL '6 months')

After some research , I've found out that JPA does not support the INTERVAL definition. Is there a workaround for archiving this particularly function ?

spring-boot jpa hibernate sql
2 Answers

You can parametrize your repository method by the date calculated inside the java code. Assuming created_date is of type Instant it would be something like:

@Query("FROM orders ord WHERE ord.created_date  >= :fromDate")
List<Order> findOrders(Instant fromDate);

and invoke it

Instant fromDate = Instant.now().minus(Duration.ofDays(182));
yourRepository.findOrders(fromDate)
user_142316190

In that case use the JPA provided functionality , where native sql code is not required.

  List<Order> findALLByCreatedDateBefore(Date fromDate);

and on java side invoke it

public void findOrders() {
    date = setMonthToJavaUtilDate(new Date(), -6);
    yourRepository.findOrders(fromDate)
    //your logic...
}

private Date setMonthToJavaUtilDate(Date date, int month) {
    Calendar now = Calendar.getInstance();
    now.setTime(date);
    now.add(Calendar.MONTH, month);
    return now.getTime();
}
user_71127120
Related