Using TIMESTAMPDIFF with JPA criteria query and hibernate as the provider

Question

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:

EntityManager entityManager = emProvider.get();

CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();

CriteriaQuery<Integer> query = criteriaBuilder.createQuery();

Root<Thingy> thingyRoot = query.from(Thingy.class);

Path<DateTime> setup = root.get("setup");
Path<DateTime> release = root.get("release");

Expression secondLiteral = criteriaBuilder.literal("SECOND");

Expression func = criteriaBuilder.function("TIMESTAMPDIFF", Integer.class, secondLiteral, setup, release);

entityManager.createQuery(query).getResultList();

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:

java.lang.IllegalStateException: No data type for node: org.hibernate.hql.internal.ast.tree.MethodNode 
 \-[METHOD_CALL] MethodNode: '('
    +-[METHOD_NAME] IdentNode: 'TIMESTAMPDIFF' {originalText=TIMESTAMPDIFF}
    \-[EXPR_LIST] SqlNode: 'exprList'
       +-[NAMED_PARAM] ParameterNode: '?' {name=param0, expectedType=null}
       +-[DOT] DotNode: 'cdr0_.setup' {propertyName=setup,dereferenceType=ALL,propertyPath=setup,path=generatedAlias0.setup,tableAlias=cdr0_,className=com.vtsl.domain.CDR,classAlias=generatedAlias0}
       |  +-[ALIAS_REF] IdentNode: 'cdr0_.id' {alias=generatedAlias0, className=com.vtsl.domain.CDR, tableAlias=cdr0_}
       |  \-[IDENT] IdentNode: 'setup' {originalText=setup}
       \-[DOT] DotNode: 'cdr0_.release' {propertyName=release,dereferenceType=ALL,propertyPath=release,path=generatedAlias0.release,tableAlias=cdr0_,className=com.vtsl.domain.CDR,classAlias=generatedAlias0}
          +-[ALIAS_REF] IdentNode: 'cdr0_.id' {alias=generatedAlias0, className=com.vtsl.domain.CDR, tableAlias=cdr0_}
          \-[IDENT] IdentNode: 'release' {originalText=release}

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

java.lang.IllegalStateException: No data type for node: org.hibernate.hql.internal.ast.tree.MethodNode 
 \-[METHOD_CALL] MethodNode: '('
    +-[METHOD_NAME] IdentNode: 'TIMESTAMPDIFF' {originalText=TIMESTAMPDIFF}
    \-[EXPR_LIST] SqlNode: 'exprList'
       +-[IDENT] IdentNode: 'SECOND' {originalText=SECOND}
       +-[DOT] DotNode: 'cdr0_.setup' {propertyName=setup,dereferenceType=ALL,propertyPath=setup,path=generatedAlias0.setup,tableAlias=cdr0_,className=com.vtsl.domain.CDR,classAlias=generatedAlias0}
       |  +-[ALIAS_REF] IdentNode: 'cdr0_.id' {alias=generatedAlias0, className=com.vtsl.domain.CDR, tableAlias=cdr0_}
       |  \-[IDENT] IdentNode: 'setup' {originalText=setup}
       \-[DOT] DotNode: 'cdr0_.release' {propertyName=release,dereferenceType=ALL,propertyPath=release,path=generatedAlias0.release,tableAlias=cdr0_,className=com.vtsl.domain.CDR,classAlias=generatedAlias0}
          +-[ALIAS_REF] IdentNode: 'cdr0_.id' {alias=generatedAlias0, className=com.vtsl.domain.CDR, tableAlias=cdr0_}
          \-[IDENT] IdentNode: 'release' {originalText=release}

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.

Principal

Hi @WiseTree, I am too facing same issue while working with grails+hibernate+MySQL. If you have a solution could you please post it? -Thanks – Magnetograph

The piece of software that I was having this issue with got postponed, so I stopped working on this before I had it figured out, unfortunately. – Principal

I really don't know if to laugh or to cry about such a big problem for such a simple request. The builders of JPA really must have full-heartedly hated SQL. I get the very same problem and still there is no way (other than hope nothing ever in the database gets longer than 35 days) – Closehauled

Answer 1

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:

CriteriaBuilder builder = em.getCriteriaBuilder();
CriteriaQuery<MyEntity> cq = builder.createQuery( MyEntity.class );
Root<MyEntity> root = cq.from( MyEntity.class );

javax.persistence.criteria.Expression<java.sql.Time> timeDiff = builder.function(
            "TIMEDIFF",
            java.sql.Time.class,
            root.<Date>get( "endDate" ),
            root.<Date>get( "startDate" ) );
javax.persistence.criteria.Expression<Integer> timeToSec = builder.function(
            "TIME_TO_SEC",
            Integer.class,
            timeDiff );

//lessThanOrEqualTo 60 minutes
cq.where( builder.lessThanOrEqualTo( timeToSec, 3600 ) );

return em.createQuery( cq ).getResultList();

And this gives me the same result.

Thallium

I got hit by the same bug, and this is probably the best solution up to now, thanks! The OP should mark this answer accepted. – Leek

Thanks, I'll just underline the key idea here: it's suggested to replace TIMESTAMPDIFF(SECOND, START_TIME, END_TIME) with TIME_TO_SEC(TIMEDIFF(START_TIME, END_TIME)). That will help to avoid SECOND literal or keyword, not sure how to call it. – Radiance

It should be noted that unlike TIMESTAMPDIFF, TIMEDIFF returns a TIME object and is limited to 35 days. – Finegrained

Answer 2

Here is explanation of equivalent JPA Criteria Query of

SELECT * from calls where TIMESTAMPDIFF(SECOND, setup, released) < 3600;

First you have to create unit expression and extend it from BasicFunctionExpression for which take "SECOND" parameter as a unit and override its rendor(RenderingContext renderingContext) method only.

import java.io.Serializable;
import org.hibernate.query.criteria.internal.CriteriaBuilderImpl;
import org.hibernate.query.criteria.internal.compile.RenderingContext;
import org.hibernate.query.criteria.internal.expression.function.BasicFunctionExpression;

public class UnitExpression extends BasicFunctionExpression<String> implements Serializable {

  public UnitExpression(CriteriaBuilderImpl criteriaBuilder, Class<String> javaType,
      String functionName) {
    super(criteriaBuilder, javaType, functionName);
  }

  @Override
  public String render(RenderingContext renderingContext) {
    return getFunctionName();
  }
}

then you use this unit expression in your JPA criteria Query.

EntityManager entityManager = emProvider.get();
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
Root<Calls> thingyRoot = query.from(Calls.class);

    Expression<String> second = new UnitExpression(null, String.class, "SECOND");
    Expression<Integer> timeInSec = cb.function(
        "TIMESTAMPDIFF",
        Integer.class,
        second ,
        root.<Timestamp>get("setup"),
        root.<Timestamp>get("release"));
    List<Predicate> conditions = new ArrayList<>();
    conditions.add(cb.lessThan(timeInSec, 3600));
    cq.where(conditions.toArray(new Predicate[]{}));
    return session.createQuery(cq);

It is working.

Mickens

There is no need to wrap one function into another function. – Mickens

Answer 3

I encountered a similar problem here with one variation - Hibernate 6 has changed enough such that the internal APIs suggested by the "OpaqueLiteralExpression" answer no longer applies.

I managed to solve this by using the raw hibernate criteria builder

cb as HibernateCriteriaBuilder
val duration = cb.durationBetween(startTime, endTime)
val overlap = cb.greaterThanOrEqualTo(cb.durationByUnit(TemporalUnit.MINUTE, duration), 10)

Inequity

Answer 4

CASE WHEN 1=1 THEN TIMESTAMPDIFF(SECOND,startDatetime,endDatetime) ELSE 0 END

Mock it with case statement.

Rodriques

Answer 5

Here's a slightly more general solution than Kalid Shah's.

Basically, what we need is a custom "opaque literal" Expression class that is capable of passing through arbitrary token expressions like MONTH, YEAR, INTERVAL 1 DAY, etc., to MySQL as function arguments:

import javax.persistence.criteria.CriteriaBuilder;

import org.hibernate.query.criteria.internal.CriteriaBuilderImpl;
import org.hibernate.query.criteria.internal.compile.RenderingContext;
import org.hibernate.query.criteria.internal.expression.LiteralExpression;

/**
 * Represents an opaque literal that gets pass through JPA unaltered into SQL.
 */
@SuppressWarnings("serial")
public class OpaqueLiteralExpression extends LiteralExpression<Void> {

    private final String value;

    public OpaqueLiteralExpression(CriteriaBuilderImpl builder, String value) {
        super(builder, Void.class, null);
        if (value == null)
            throw new IllegalArgumentException("null value");
        this.value = value;
    }

// ExpressionImpl

    @Override
    public String render(RenderingContext renderingContext) {
        return this.value;
    }
}

Then you can use it like this:

final Expression<LocalDate> birthDate = student.get(Student_.birthDate);
final Expression<Integer> age = builder.function("TIMESTAMPDIFF", Integer.class,
  new OpaqueLiteralExpression(builder, "YEAR"), birthDate, LocalDate.now());

Battery