Spring Data JPA - Query with The Date Minus 2 Days Not Working

Spring Data JPA - query with the date minus 2 days not working

Actually, JPA doesn't support time periods operations because not all databases support it. So you have following options:

1. Calculate date programmatically (i.e. using calendar API or Java 8 Date Time API)
2. Use native query
3. Use concrete implementation if your JPA provider allows that. But AFAIK popular JPA providers don't give you such an option.

Subtract 6 months from current date in Spring query

This is what I was looking for.

 Query query = em.createNativeQuery("SELECT" +
    "  ips.*,
     "  FROM test1.ilist_property_summary ips " +
     "  WHERE ( ips.statistics_date between date_sub(curdate(), interval 6 month) and curdate() OR ips.statistics_date IS NULL)");

Spring data query for localdate returns wrong entries - minus one day

There was a bug in the MySQL Connector/J earlier than version 8.0.22. Updating to 8.0.22 will fix the issue.

From the 8.0.22 changelog:

LocalDate, LocalDateTime, and LocalTime values set through Connector/J
were altered when there was a timezone difference between the server
and the client. This fix corrects the issue by handling the LocalDate,
LocalTime, and LocalDateTime with no time zone conversion and no
intermediate conversions to other date-time classes.

The underlying bug:
https://bugs.mysql.com/bug.php?id=93444

Count number of days between 2 dates in JPA

It looks like you are looking for a solution with JPQL to perform queries like SELECT p FROM Period p WHERE datediff(p.to, p.from) > 10.

I'm afraid there is no such functionality in JPQL so I recommend using native SQL. Your idea if extending Dialect with Hibernate's SQLFunctionTemplate was very clever. I'd rather change it to use DATE_PART('day', end - start) as this is the way to achieve days difference between dates with PostgreSQL.

You might also define your function in PostgreSQL and using it with criteria function().

'CREATE OR REPLACE FUNCTION "datediff"(TIMESTAMP,TIMESTAMP) RETURNS integer AS \'DATE_PART('day', $1 - $2);\' LANGUAGE sql;'

cb.function("datediff", Integer.class, end, start);

Get records for last 3 days via Spring JPA Repository

I suggest to split the logic from the actual queries. A service could handle all the intermediate things, e.g.:

@Service
public class MessageService {
    private final MessageRepository repository;

    @Autowired
    public MessageService(MessageRepository repository) {
        this.repository = repository;
    }

    List<Message> getLastThreeDays() {
        // subtract 3 days from today
        LocalDate threeDaysAgoDate = LocalDate.now().minusDays(3);
        return this.repository.findAllWithDateAfter(threeDaysAgoDate);

    }
}

and your repository stays nice and clean:

@Repository
public interface MessageRepository extends JpaRepository<Message, Long> {

    Optional<Message> findByMessageId(Long id);

    @Query("select m from Message m where date >= :threeDaysAgoDate")
    List<Message> findAllWithDateAfter(@Param("threeDaysAgoDate") LocalDate threeDaysAgoDate);

}

Adding X hours - @Query - Spring Data JPA

Like a_horse_with_no_name said I had to use native query to do something like that so I changed my method to:

  @Query(value = "select * from activation_codes a where a.type = :type and a.id_account = :id_account and a.creation_time <= NOW() - :hoursAgo * INTERVAL '1 hour'", nativeQuery = true)
  List<ActivationCode> getAllAddedAtLeastXHoursAgo(@Param("id_account") int id_account, @Param("type") int type, @Param("hoursAgo") int hoursAgo);

And it works correct.
Thanks for all help.

---

Related Topics