calculate date difference in JPA / JPQL query using PostgreSQL and QueryDSL

Unfortunately JPA or JQL currently did not support a date difference function. So you have to use native database functions. In PostgreSQL you would use date_part like:

SELECT DATE_PART('day', date2 - date1) FROM mytable

JPA only supports to call functions with simple parameters, but not the calculation of two dates. So you have the problem to subtract one date or timestamp from another. I used a custom function to solve this problem:

CREATE OR REPLACE FUNCTION datediff(a TIMESTAMP, b TIMESTAMP) RETURNS integer AS $$
BEGIN
    RETURN DATE_PART('day', a - b);
END;
$$ LANGUAGE plpgsql

So you can use this function with two simple parameters.

But how to use this function in QueryDSL? This is quite easy. You just have to define a template, especially a number template in this example. You can use the static methods in class Expressions to create such templates. This template is then easily usable in the next query.

QMyTable myTable = QMyTable.myTable;
final NumberTemplate<Integer> template = Expressions.numberTemplate(Integer.class, "function('datediff', {0}, {1})", myTable.date1, myTable.date2);
JPAQuery<MyTable> query = new JPAQuery<>(getEntityManager()).select(myTable).from(myTable).where(template.goe(5));
return query.fetch();