How to query JPA LocalDateTime field with a LocalDate value?

Question

I search for a way to get a list of Objects created on a certain LocalDateTime date saved in the Postgresql database in a field of type TIMESTAMPTZ.

To do so, I tried to use JpaRepository:

List<Object> findByCreationDate(LocalDate date);

But I get the error:

java.lang.IllegalArgumentException: Parameter value [2020-12-12] did not match expected type [java.time.LocalDateTime (n/a)]

I also tried writing the query myself with the same result.

The solutions I thought about so far :

1. To get all the objects and filter in Java (but I wouldn't want to go there, so no)

2. To convert the LocalDate parameter in LocalDateTime (but I assume in this case I will see only Objects created at the same exact time, so no. I would consider this option only when I'm sure the fields of time are midnight).

3. To create a method findByCreationDateBetween(LocalDateTime startOfTheDay, LocalDateTime endOfTheDay) (interesting option, but I would like to do the query without modifying the LocalDate and converting it to LocalDateTime at the start and at the end of the day.)

I also tried to find some functions that would be able to 'cast' the LocalDateTime in LocalDate in the query or compare the year, the month and the day of the LocalDate and LocalDateTime, but unsuccessfully. The type of creationDate should remain LocalDateTime and the database field of type TIMESTAMPTZ.

Are there any other Jpa @Query alternatives and overall, what would be the best approach for this case ?

Answer 1

Using an answer that was quickly deleted after it was posted (I had to use the column and not the property name, mark the query as nativeQuery and also select with '.*' to avoid org.postgresql.util.PSQLException: The column name id was not found in this ResultSet error), I found this solution:

@Query(value = "SELECT e.* FROM Entity e WHERE DATE(creation_date) =:date", nativeQuery = true)
List<Entity> findByCreationDate(LocalDate date);

Alternatively, following this answer, I tested with success a second solution:

default List<Entity> findByCreationDate(LocalDate localDate) {
    return findByPublicationDateBetween(localDate.atStartOfDay(), localDate.plusDays(1).atStartOfDay());
}


List<Entity> findByCreationDateBetween(LocalDateTime from, LocalDateTime to);