JPA – Trunc date in JPQL query (Oracle)

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)




Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de

Estás comentando usando tu cuenta de Cerrar sesión /  Cambiar )

Google photo

Estás comentando usando tu cuenta de Google. Cerrar sesión /  Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión /  Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión /  Cambiar )

Conectando a %s