How to Subtract number of days from current date in HQL query

Question

I am using HQL to get the data inserted exact 21 days from now. Here is my Code

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()-7 I got the correct data of the date 2016-07-04. The calculation for dat is like;

2016-07-11 - 7 = 20160704
2016-07-11 - 21 = 20160690

I also Tired using INTERVAL which is for native sqlQuery. Here is my code for using INTERVAL in 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.

So what can I use instead of subtracting the day like this: CURDATE()-21? in HQL only

Answer 1

I have solved the issue by using one native SQL query which can get me the exact date.

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:

Query queryThreeWeek = session.createQuery("from Users where createdDate = :createdDate");
queryThreeWeek.setParameter("createdDate", sub3WeekList.get(0).toString());
List<Users> userDetailsThreeWeekList = queryThreeWeek.list();

Answer 2

You can use date_Sub in a native SQL query (not a HQL query!):

 "from Users where createdDate =   DATE( DATE_SUB( NOW() , INTERVAL 21 DAY ) )" 

Answer 3

The solution with HQL is quite simple:

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