java 如何在 HQL 查询中从当前日期减去天数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/38309016/
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
How to Subtract number of days from current date in HQL query
提问by Shivam
I am using HQL to get the data inserted exact 21 days from now. Here is my Code
我正在使用 HQL 从现在开始准确地插入 21 天后的数据。这是我的代码
Query queryThreeWeek = session.createQuery("from Users where createdDate = CURDATE()-21");
List<Users> userDetailsThreeWeekList = queryThreeWeek.list();
I can not use createSQLQuery.
Right now I am not getting any data, but there is data for the date 2016-06-20. And that is because of the month changed because when I used CURDATE()-7I got the correct data of the date 2016-07-04.
The calculation for dat is like;
我不能用createSQLQuery。现在我没有得到任何数据，但是有 date 的数据2016-06-20。那是因为月份改变了，因为当我使用时CURDATE()-7我得到了正确的日期数据2016-07-04。dat 的计算就像;
2016-07-11 - 7 = 20160704
2016-07-11 - 21 = 20160690
I also Tired using INTERVALwhich is for native sqlQuery. Here is my code for using INTERVALin HQL:
我也厌倦了使用INTERVALwhich is for native sqlQuery。这是我INTERVAL在 HQL 中使用的代码：
Query queryThreeWeek = session.createQuery("from Users where createdDate = DATE( DATE_SUB( NOW() , INTERVAL 21 DAY ) )");
List<Users> userDetailsThreeWeekList = queryThreeWeek.list();
Also tried
也试过
Query queryThreeWeek = session.createQuery("from Users where createdDate = DATE( DATE_SUB( CURDATE() , INTERVAL 21 DAY ) )");
List<Users> userDetailsThreeWeekList = queryThreeWeek.list();
but it is giving me exception like: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: 21.
但它给我的异常，如：org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: 21。
So what can I use instead of subtracting the day like this: CURDATE()-21? in HQL only
那么我可以使用什么而不是像这样减去一天：CURDATE()-21？仅在 HQL 中
采纳答案by Shivam
I have solved the issue by using one native SQL query which can get me the exact date.
我已经通过使用一个本机 SQL 查询解决了这个问题，该查询可以为我提供确切的日期。
Query sub3Week = session.createSQLQuery("select DATE( DATE_SUB( CURDATE() , INTERVAL 21 DAY ) ) from dual");
List<Date> sub3WeekList = sub3Week.list();
And then I use this data in the HQL query like this:
然后我在 HQL 查询中使用这些数据，如下所示：
Query queryThreeWeek = session.createQuery("from Users where createdDate = :createdDate");
queryThreeWeek.setParameter("createdDate", sub3WeekList.get(0).toString());
List<Users> userDetailsThreeWeekList = queryThreeWeek.list();
回答by scaisEdge
You can use date_Sub in a native SQLquery (not a HQL query!):
您可以在本机 SQL查询（不是 HQL 查询！）中使用 date_Sub ：
"from Users where createdDate = DATE( DATE_SUB( NOW() , INTERVAL 21 DAY ) )"
回答by Datz
The solution with HQLis quite simple:
HQL的解决方案非常简单：
final long time = System.currentTimeMillis() - java.time.Duration.ofDays(21).toMillis();
final javax.persistence.Query query = entityManagerOrSession.createQuery(
"SELECT x FROM users x WHERE x.createddate> :time");
query.setParameter("time", new java.sql.Timestamp(time));
