Oracle defines the date data type as follows
The DATE datatype stores point-in-time values (dates and times) in a table. The DATE datatype stores the year (including the century), the month, the day, the hours, the minutes, and the seconds (after midnight).
It differs from other databases as it stores date and time information. For example, MySQL defines 3 different types for this purpose: DATE, DATETIME, and TIMESTAMP
You should take this into account when comparing and storing values.
It is very common to use the
TRUNC function when searching within a range of dates.
Fortunately, with Oracle, you can use the TRUNC function on your JPQL
@Query("select a from Asistencia a where TRUNC(a.created_at) between ?1 and ?2") List<Asistencia> findByFechaBetween(@Temporal Date inicio, @Temporal Date fin);
I’ve used the Temporal annotation so Spring would trunc the date parameters before it passes them to the SQL database.
If you’re not using Oracle and need to use the TRUNC function, you will have to recur to other options. The easiest, though, would be to write a native query and use your database trunc implementation. Another really useful workaround is to extend the second parameter to the start of the next day, this way it will include any time throughout the previous day.
It’s a shame it isn’t included in standard JPQL to allow your software to be database agnostic (one main advantage of using JPA)