Java 计算JPA中两个日期之间的天数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24991581/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Count number of days between 2 dates in JPA
提问by Arnaud Denoyelle
I need to count the number of days between 2 dates in JPA.
我需要计算 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 Numberand the Datedoes not extend Number.
1）CriteriaBuilder.diff()。但它不会编译，因为此方法需要 someN extends Number并且Date不扩展Number.
2) I tried to extend the PostgreSQL82Dialect(as my target database is PostgreSQL) :
2）我试图扩展PostgreSQL82Dialect（因为我的目标数据库是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).
这会编译并且请求成功，但返回的结果不一致（今天和明天之间是 78 天）。
How would you do this?
你会怎么做？
采纳答案by zbig
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.
看起来您正在寻找使用 JPQL 的解决方案来执行诸如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 SQLFunctionTemplatewas 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.
恐怕 JPQL 中没有这样的功能，所以我建议使用本机 SQL。如果使用 Hibernate 扩展方言，您的想法SQLFunctionTemplate非常聪明。我宁愿将其更改为使用，DATE_PART('day', end - start)因为这是使用 PostgreSQL 实现日期之间天差的方法。
You might also define your function in PostgreSQL and using it with criteria function().
您还可以在 PostgreSQL 中function()定义您的函数并将其与标准一起使用。
'CREATE OR REPLACE FUNCTION "datediff"(TIMESTAMP,TIMESTAMP) RETURNS integer AS \'DATE_PART('day', - );\' LANGUAGE sql;'
cb.function("datediff", Integer.class, end, start);
回答by hcl
1) CriteriaBuilder.diff(). but it does not compile because this method expects some N extends Number and the Date does not extend Number.
1) CriteriaBuilder.diff()。但它不会编译，因为此方法需要一些 N 扩展数字，而日期不扩展数字。
Try to use no of mili seconds for each date as shown below.
尝试为每个日期使用 no 毫秒，如下所示。
Date date = new Date()//use your required date
long millisecond = date.getTime();//Returns no of mili seconds from 1 Jan, 1970 GMT
Long in Number in java and according to autoboxing you can use this. May be this can help.
Java中的Long in Number，根据自动装箱，您可以使用它。可能这会有所帮助。
回答by Neil Stockton
JPA 2.1 provides for use of "FUNCTION(funcName, args)" in JPQL statements. That allows such handling.
JPA 2.1 规定在 JPQL 语句中使用“FUNCTION(funcName, args)”。这允许这样的处理。
回答by Arnaud Denoyelle
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 questionin order to know if this behavior is a bug or a feature :
我打开了一个新问题，以了解此行为是错误还是功能：
