JPA CriteriaQuery calculating datetime difference for use in where predicate

Question

I am trying to select entities from my database that have a time difference between 2 fields less than or greater to some value. In standard SQL, this is simply accomplished using the TIMESTAMPDIFF function:

SELECT * from run where TIMESTAMPDIFF(SECOND, run.end_time, run.start_time) > 60;

However, there doesn't seem to be any equivalent in JPA 2.0.

I've tried everything I can think of, including the suggestions here: Using TIMESTAMPDIFF with JPA criteria query and hibernate as the provider

predicate.getExpressions().add(criteriaBuilder.greaterThanOrEqualTo(criteriaBuilder.function("TIMESTAMPDIFF", Long.class, criteriaBuilder.literal("SECOND"), root.get(Run_.endTime), root.get(Run_.startTime)), minSeconds))

simply doesn't work and since I am specifically trying to calculate large durations (60/90 days). The TIMEDIFF solution doesn't help because the max TIMEDIFF that can be returned is 35 days. Is there some other way to accomplish this?

Answer 1

Here is explanation of equivalent JPA Criteria Query of

SELECT * from run where TIMESTAMPDIFF(SECOND, run.end_time, run.start_time) > 60;

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.

    CriteriaBuilder cb = session.getCriteriaBuilder();
    CriteriaQuery<Run> cq = cb.createQuery(Run.class);
    Root<Run> root = cq.from(Run.class);

    Expression<String> second = new UnitExpression(null, String.class, "SECOND");
    Expression<Integer> timeDiff = cb.function(
        "TIMESTAMPDIFF",
        Integer.class,
        second,
        root.<Timestamp>get(Run_.endTime),
        root.<Timestamp>get(Run_.startTime));
    List<Predicate> conditions = new ArrayList<>();
    conditions.add(cb.greaterThan(timeDiff, 60));
    cq.where(conditions.toArray(new Predicate[]{}));
    return session.createQuery(cq);

It is working.

Answer 2

Expression<String> month = new MyFunctionExpression(null, String.class, "MONTH");
Expression<Integer> diff = builder.function("timestampdiff", Integer.class, month, root.get(Run_.endTime), root.get(Run_.startTime));
predicates.add(builder.gt(diff, 60));

MyFunctionExpression extends BasicFunctionExpression and override the render method.

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