Count number of days between 2 dates in JPA

Question

I need to count the number of days between 2 dates in JPA.

For example :

CriteriaBuilder.construct(
  MyCustomBean.class
  myBean.get(MyBean_.beginDate), //Expression<Date>
  myBean.get(MyBean_.endDate), //Expression<Date>
  myDiffExpr(myBean) //How to write this expression from the 2 Expression<Date>?
);

So far, I tried :

1. CriteriaBuilder.diff(). but it does not compile because this method expects some N extends Number and the Date does not extend Number.

2. I tried to extend the PostgreSQL82Dialect (as my target database is PostgreSQL) :

   public class MyDialect extends PostgreSQL82Dialect {
   
     public MyDialect() {
       super();
       registerFunction("datediff", 
       //In PostgreSQL, date2 - date1 returns the number of days between them.
       new SQLFunctionTemplate(StandardBasicTypes.LONG, " (?2 - ?1) "));
     }
   }
   

This compiles and the request succeeds but the returned result is not consistent (78 days between today and tomorrow).

How would you do this?

Answer 1

It looks like you are looking for a solution with JPQL to perform queries like SELECT p FROM Period p WHERE datediff(p.to, p.from) > 10.

I'm afraid there is no such functionality in JPQL so I recommend using native SQL. Your idea if extending Dialect with Hibernate's SQLFunctionTemplate was very clever. I'd rather change it to use DATE_PART('day', end - start) as this is the way to achieve days difference between dates with PostgreSQL.

You might also define your function in PostgreSQL and using it with criteria function().

'CREATE OR REPLACE FUNCTION "datediff"(TIMESTAMP,TIMESTAMP) RETURNS integer AS \'DATE_PART('day', $1 - $2);\' LANGUAGE sql;'

cb.function("datediff", Integer.class, end, start);