Skip to content
Advertisement

Using TIMESTAMPDIFF with JPA criteria query and hibernate as the provider

I have a data table with columns setup and release, both of which hold timestamps. My goal is to create an equivalent of the SQL query below using CriteriaQuery.

SQL Query:SELECT TIMESTAMPDIFF(SECOND, setup, released)) as sum_duration FROM calls

The CriteriaBuilder#diff() function clearly does not work as it requires parameters that are Numbers, so I tried using CriteriaBuilder#function:

However, when I tried running this code it threw an exception; it appears that the literal was not rendered as a constant, but rather as a parameter:

So I tried anonymously overriding the LiteralExpression#render to directly return the string I supply to the method, however that thew this exception.

So the question is: How can I either fix this operation I’m trying to do, or achieve the original goal?

I’m using Hibernate, my database is MySQL.

Advertisement

Answer

I bumped into the same problem: the SECOND will be surrounded by apostrophes and the query will throw an exception.

I worked around it by the following code:

And this gives me the same result.

User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement