Count number of days between 2 dates in JPA

java postgresql jpa

18,706

Solution 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);

Solution 2

I finally found that the problem comes from the fact that the order of the parameters is not the one I expected :

/*
 *(?2 - ?1) is actually equivalent to (? - ?).
 * Hence, when I expect it to evaluate (date2 - date1), 
 * it will actually be evaluated to (date1 - date2)
 */
new SQLFunctionTemplate(StandardBasicTypes.LONG, " (?2 - ?1) "));

I opened a new question in order to know if this behavior is a bug or a feature :

Solution 3

JPA 2.1 provides for use of "FUNCTION(funcName, args)" in JPQL statements. That allows such handling.

18,706

Author by

Arnaud Denoyelle

Updated on June 21, 2022

Comments

Arnaud Denoyelle almost 2 years

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 :

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

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?

Arnaud Denoyelle almost 10 years

My dates are acutally Expression<Date>. How is it possible to extract the dateTime of it? (For example, building an Expression<Long> that I could use with CriteriaBuilder.diff ? I edited the question to make it clearer.
Arnaud Denoyelle almost 10 years

I tried but as end - start is considered as an Integer by Postgres, the corresponding function does not work. My problem might not come from the template but maybe from the way I use it. I am still investigating.
zbig almost 10 years

end - start in PostgreSQL should produce INTERVAL type. I don't think there is corresponding Java type.
Craig Ringer almost 10 years

The fact that JPA can't even subtract dates says a lot about how useful it is in the real world.
zbig almost 10 years

I wouldn't go that far. JPQL, which is a query language of JPA cannot do it. But JPA? Sure can. Just select two dates so you can have it as Java objects, and you can subtract it many different ways.
Arnaud Denoyelle almost 10 years

@zbigniewTomczak I finally found that the problem came from the (?2 - ?1) part. I opened a new question about it : stackoverflow.com/questions/25037380/…
zbig almost 10 years

The implementation in Hibernate looks good to me. I didn't change from ver 3.3.0. Have you confirmed this in your logs? Are you sure that is not a "thing" with transforming PostgreSQL Interval type to Java long type?
Arnaud Denoyelle almost 10 years

@zbigniewTomczak I found this by modifying the postgres configuration file in order to make it log the full request (with parameters values). I use Hibernate 4.3.1.Final. I do not use PostgreSQL intervals. I give 2 Expression<Date> and PostgreSQL returns an integer which represents the number of days between the dates. I changed ?2 - ?1 to -?1 + ?2 and it worked.
bruno over 4 years

And what would be the Oracle function to call for date subtraction?