Timestamp literal in JPQL typed query with Hibernate

Question

Is there a way to use timestamp literals in JPQL queries with Hibernate?

There is a constant timestamp to compare to which should be placed in JPQL query. Now it works this way

TypedQuery<TestEntity> query = entityManager
.createQuery("select t from TestEntity t where greatest(nvl(t.f1, :nullDate), nvl(t.f2, :nullDate)) between :date1 and :date2")
.setParameter("date1", date1)
.setParameter("date2", date2)
.setParameter("nulllDate", LocalDateTime.of(1970, 1, 1, 0, 0, 0, 1))

I've tried (solution found here and here) to change the query to the following

select t from TestEntity t where greatest(nvl(t.f1, {ts '1970-01-01 00:00:00:000000001'}), nvl(t.f2, {ts '1970-01-01 00:00:00:000000001'})) between :startChanges and :endChanges

but got an error in runtime

org.hibernate.QueryException: unexpected char: '{' [select t from TestEntity t where greatest(nvl(t.f1, {ts '1970-01-01 00:00:00:000000001'}), nvl(t.f2, {ts '1970-01-01 00:00:00:000000001'})) between :startChanges and :endChanges]

My setup is
Java 8
JPA 2.1
Spring Boot 1.4.2.RELEASE
Hibernate 5.1.0.Final (reproduced also with 5.2.16.Final)
Oracle 11.2

UPD: maybe it's not on the spec, but greatest/nvl functions are passed from JPQL to SQL correctly with the code above. I've tried to wrap greatest/nvl in function but the literals are still not accepted.

Where does the JPA spec define a JPQL function of greatest ? or nvl? Answer : it doesn't — user8558216
@DN1 I've replaced the query string with a more strict JPQL calling greatest/nvl via "function", but it doesn't change anything. It still accepts parameter and generates the same SQL for it while throwing an unexpected char: '{' error for literal. select t from TestEntity t where function('greatest', function('nvl', t.f1, {ts '1970-01-01 00:00:00.000000001'}), function('nvl', t.f2, {ts '1970-01-01 00:00:00.000000001'})) between :startChanges and :endChanges — dfche
I can use JDBC escape syntax (what you're using with the curly brackets and "ts") with no issue with my JPA provider (and your syntax usage is valid IMHO), as per datanucleus.org:15080/products/accessplatform_5_1/jpa/… Maybe try a simple query without the function calls, and with the JDBC escape syntax to check that your JPA provider handles it correctly first, then add in the FUNCTIONs. — user8558216
@DN1 I've tried a very basic query with no conditions but one with the literal. It results in the same error, looks like Hibernate 5.1.0 doesn't support timestamp literals, at least with this syntax. This was an issue for earlier versions at least stackoverflow.com/questions/8386109/… — dfche

Alberto Alberto · Accepted Answer · 2018-04-25T07:41:42

I have prepared a test in my enviroment, and this returns results:

Calendar calendar = Calendar.getInstance();
calendar.setTime(date1);
calendar.set(Calendar.HOUR, 0);
calendar.set(Calendar.MINUTE, 0);
calendar.set(Calendar.SECOND, 0);
calendar.set(Calendar.MILLISECOND, 0);

date1 = calendar.getTime();

calendar.setTime(date2);
calendar.set(Calendar.HOUR, 0);
calendar.set(Calendar.MINUTE, 0);
calendar.set(Calendar.SECOND, 0);
calendar.set(Calendar.MILLISECOND, 0);

date2 = calendar.getTime();

TypedQuery<TestEntity> query = entityManager
.createQuery("select t from TestEntity t where greatest(nvl(t.f1, TO_TIMESTAMP('1970-01-01 00:00:00,000000001', 'YYYY-MM-DD HH24:MI:SS,FF9')), nvl(t.f2, TO_TIMESTAMP('1970-01-01 00:00:00,000000001', 'YYYY-MM-DD HH24:MI:SS,FF9')) between :date1 and :date2")
.setParameter("date1", date1)
.setParameter("date2", date2);

I hope this is what you're looking for.

EDIT: Modified query to add TO_TIMESTAMP function. Confirmed by @dfche it is working

Timestamp literal in JPQL typed query with Hibernate

1 Answers