JPA LocalDateTime Queries

JPAQueryLocalDateTime.md

In JPA 2.1 to compare dates as LocalDateTimes, there is need to use the SQL function "CAST" on the JPQL Query

Example:

• We want to retrieve the number of actions occured on a certain System during a period of time

The entity:

@Entity
class SystemAction{
  /**
   * LocalDateTime with the date which the action occured
   */
  private LocalDateTime actionDate;
}

The function:

public long getActionsOccurencesOnPeriodOfTime(LocalDateTime dateX,LocalDateTime dateY){
  return entityManager.createQuery("SELECT COUNT(SA) FROM SystemAction SA WHERE CAST(SA.actionDate as timestamp) BETWEEN :dateX AND :dateY")
                                  .setParameter("dateX",dateX)
                                  .setParameter("dateY",dateY)
                                  .getSingleResult();
}