Compare difference between two dates using JPA Criteria Query

criteria-api, datediff, java, jpa

Solution

Supposing that `created` is a `java.util.Date`, you have to perform the `DATEDIFF` in java and compare the property with the resulting date:

Calendar c = Calendar.getInstance();
c.add(Calendar.DAY_OF_YEAR, -20);
Date myDate = c.getTime();

predicates.add(cb.lessThanOrEqualTo(root.<Date>get("created"), myDate));

Problem

I am using MySQL as a backend for my application. I want to execute query below using JPA Criteria Query. ``` SELECT * FROM table1 t1 WHERE datediff(now(), t1.created) >= 20; ``` Note: Type of `created` is `TIMESTAMP` I want Criteria Query like: ``` CriteriaBuilder cb = getEntityManager().getCriteriaBuilder(); // select CriteriaQuery<Table1> query = cb.createQuery(Table1.class); // from Root<Table1> root = query.from(Table1.class); // where List<Predicate> predicates = new ArrayList<Predicate>(); // TODO: add expression for datediff(now(), t1.created) >= 20 query.where(cb.and(predicates.toArray(new Predicate[0]))); ``` Can any one help? Thanks in advance.

Original source