JPA Criteria API: Difference between two dates in database?

Question

Simple Question: I have a (MySQL) table with two date columns (from, until) and ask it with

select * from mytable where until > from + interval 4 week;

This is really simple in MySQL.

How could you do that in JPA Query like

cq.where(cb.and(mytable_.get(until),...)

EDIT: Both dates come from the database row, I compare two fields of the database and not one field from application with one from database.

see http://stackoverflow.com/questions/9449003/compare-date-entities-in-jpa-criteria-api — Mark Sholund, Nov 03 '15 at 13:04
@MarkS. In all the examples that I have seen, also in your example, is one date fixed from application (param in your link). My question has two dates from database. — flaschenpost, Nov 03 '15 at 13:10
whether you have one date parameter or two dates from fields is irrelevant to the question ... the Criteria API has methods for both ways. What you can't do simply in JPA Criteria is the "+ interval 4 week". — Neil Stockton, Nov 03 '15 at 13:30
So there is no way to find reservations longer than a certain interval? That seems strange. — flaschenpost, Nov 03 '15 at 13:40
You can use `CriteriaBuilder#function()` https://docs.oracle.com/javaee/6/api/javax/persistence/criteria/CriteriaBuilder.html although this break compatibility with different dbs, or if you can alter the table structure, you can add a column with the date difference. — perissf, Nov 03 '15 at 13:46

score 7 · Accepted Answer · answered Sep 11 '18 at 06:11

Here is the Explanation of Equivalent JPA Criteria Query of

select * from mytable where until > from + interval 4 week;

First you have to create unit expression and extend it from BasicFunctionExpression for which take "WEEK" 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<MyTable> cq = cb.createQuery(MyTable.class);
    Root<MyTable> root = cq.from(MyTable.class);

    Expression<String> week= new UnitExpression(null, String.class, "WEEK");
    Expression<Integer> timeDiff = cb.function(
        "TIMESTAMPDIFF",
        Integer.class,
        week,
        root.<Timestamp>get(MyTable_.until),
        root.<Timestamp>get(MyTable_.from));
    List<Predicate> conditions = new ArrayList<>();
    conditions.add(cb.greaterThan(timeDiff, 4));
    cq.where(conditions.toArray(new Predicate[]{}));
    return session.createQuery(cq);

It is working fine.

Cool, that looks easy enough. If such a `UnitExpression` would be part of the libraries, a lot of expressions could be made easier. — flaschenpost, Sep 12 '18 at 07:22
Instead of creating an entire class for it, couldn't you just have used `Expression week= cb.literal("WEEK");` — Powerlord, Mar 04 '20 at 19:54

flaschenpost · Answer 2 · 2015-11-05T06:49:09.897

EDIT2: workaround found

Since we have to work only with functions that don't need a built-in parameter (like WEEK), I ended up with

cb.greaterThan(
  cb.diff(
    cb.function("unix_timestamp", Long.class, root.get(Table_.until)),
    cb.function("unix_timestamp", Long.class, root.get(Table_.from))
  )
, 3600L*longerThanHours)

For reference a version that leads to a dead end:

There is no way to make it work like this, you can not send "hour" without surrounding "" as a parameter to the database.

CriteriaBuilder cb = ...;
CriteriaQuery<MyTable> cq = cb.createQuery(MyTable.class);
Root<MyTable> mytable = cq.from(MyTable.class);

cb.greaterThan(
   cb.function(
      "timestampdiff"
      , Integer.class
      , WEEK // <-- this is where JPA gets unable to create SQL
      , mytable.get(MyTable_.from)
      , mytable.get(MyTable_.until)
   )
   , 4
)

Read my answer. It is possible to pass a "WEEK" parameter in function. — Khalid Shah, Sep 11 '18 at 06:12

score 0 · Answer 3 · answered Feb 16 '21 at 09:00

**A sample example using criteria api to fetch entity between two date ** where date range is give between startDate and endDate :-

public EntityClass getResultEntities(Date startDate, Date endDate){
          CriteriaBuilder builder = em.getCriteriaBuilder();
          CriteriaQuery<EntityClass> criteria = builder.createQuery(EntityClass.class);
          Root<OrderImpl> entityClass = criteria.from(EntityClassImpl.class);
          criteria.select(entityClass);
          criteria.where(builder.between(entityClass.<Date>get("submitDate"), startDate, endDate));
          criteria.orderBy(builder.desc(entityClass.get("submitDate")));
          TypedQuery<EntityClass> query = em.createQuery(criteria);
          return query.getResultList();
    }

JPA Criteria API: Difference between two dates in database?

3 Answers3