Geek Answers Handbook

Specify the number of days between two dates in JPA

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

For instance:

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 have tried:

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

2) I tried to extend PostgreSQL82Dialect (since 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 return result is incompatible (78 days between today and tomorrow).

How do you do this?

+2
source share
4 answers

Finally, I found that the problem is that the order of the parameters is not the same as 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 to find out if this behavior is a bug or function:

+1
source

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

I am afraid that such a function does not exist in JPQL, so I recommend using your own SQL. Your idea if the Dialect extension with Hibernate SQLFunctionTemplate was very smart. I would prefer to change it to use DATE_PART('day', end - start) , as this is a way to achieve the difference between dates with PostgreSQL.

You can also define your function in PostgreSQL and use it with function() criteria .

 'CREATE OR REPLACE FUNCTION "datediff"(TIMESTAMP,TIMESTAMP) RETURNS integer AS \'DATE_PART('day', $1 - $2);\' LANGUAGE sql;' cb.function("datediff", Integer.class, end, start);
+1
source

JPA 2.1 provides for the use of "FUNCTION (funcName, args)" in JPQL statements. This allows processing.

+1
source

1) CriteriaBuilder.diff (). but it does not compile because this method expects some N extensions of Number and Date to not increment a number.

Try not to use milliseconds seconds for each date, as shown below.

 Date date = new Date()//use your required date long millisecond = date.getTime();//Returns no of mili seconds from 1 Jan, 1970 GMT

Long number in java and according to autoboxing you can use this. Maybe this can help.

0
source

More articles:


All Articles