JPA – Trunc date in JPQL query (Oracle)

mayo 18, 2015

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)

sources:
https://docs.oracle.com/cd/E17952_01/refman-5.1-en/datetime.html
http://stackoverflow.com/questions/8166258/compare-only-date-component-in-hql
https://community.oracle.com/thread/1057255

Anuncios

Responder

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

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. 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 )

Google+ photo

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

Conectando a %s

A %d blogueros les gusta esto: