How to Subtract Number of Days from Current Date in Hql Query

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

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

How to subtract one day from current date then convert to string in Hive

The problem is the way you trying to subtract a day from date.I would suggest to subtract number of seconds in a day(86400) from unix timestamp in where clause-

CAST(TO_DATE(FROM_UNIXTIME(UNIX_TIMESTAMP()-86400))as date) 

HQL diff 2 date in days

tray to cast (i.smeEnd - :currentDate) to integer in where clause

(i.smeEnd - :currentDate) ::integer <= :days

or as cast ...

cast ((i.smeEnd - :currentDate) as integer) <= :days

if i'm not wrong, something similar did happen to college of mine a while ago. something with java checking passed parameter conditions, it checks ":currentDate) <= :days" conversions from other data types, pre checking this condition and assuming that both parameters must be dates ignoring that bracket, cant remember details . i rarely work with java, nor this is my homelanguage

but hope this helps

edited 2015-11-06

sorry, cant comment yet not enough respect points :)
to ansver your coment

"What different between cast ((i.smeEnd - :currentDate) as integer) and EXTRACT(EPOCH FROM date_trunc('day', age(i.smeEnd, :currentDate))) / 60 / 60 / 24 ? Two variant are working"

you must understand, both are working because query syntax is different.
problem occurs with java (hibernate?) query parser is a bit "smart" , pre checking if query is all right, it finds in the query a condition with 2 parameters ":currentDate) <= :days" and fails at it, because it tries to precheck if condition is valid, dont know the details, this same problem can occur when you have some other conditions with different parameter types next to each other which logic encapsulated with brackets.

if you put anything between ":currentDate [here] ) or [here] <= :days" that query will work for you, I bet that if you switch those two values
(i.smeEnd - :currentDate) <= :days
to

(- :currentDate + i.smeEnd ) <= :days

that query will work too

Can HQL substract or sum date fields?

This depends on whether you need this to work across all databases or not.

In the latter case (specific database) check your dialect for available date functions.
MySQL dialect, for example, supports timediff() and time_to_sec() functions, which would allow you to write your query as:

select o from order o 
  left join o.timingSettings ts
 where time_to_sec(timediff(o.time, current_timestamp())) < ts.orderTimeout

If you need to do this for all databases (or if your database doesn't support needed date functions), Hibernate supports year(), month(), ... second() functions across all dialects. Using these will make your query rather verbose :-) plus you'll have issues with DST.

How to perform date operations in hibernate

See
Performing Date/Time Math In HQL?
for an example.

To use custom sql you must wrote an own hibernate dialect and register:

registerFunction("weekday", 
  new SQLFunctionTemplate(Hibernate.INTEGER, "to_char(?1,'D')") );

Performing Date/Time Math In HQL?

If you need the database server's time, you could first do a simple hql query to get the timestamp and then calculate the maxTimestamp in java and pass the fetched timestamp and the calculated maxTimeStamp to a query like the one of ccclark.

SQL: Subtracting 1 day from a timestamp date

Use the INTERVAL type to it. E.g:

--yesterday
SELECT NOW() - INTERVAL '1 DAY';

--Unrelated: PostgreSQL also supports some interesting shortcuts:
SELECT 
    'yesterday'::TIMESTAMP, 
    'tomorrow'::TIMESTAMP, 
    'allballs'::TIME AS aka_midnight;

You can do the following then:

SELECT 
    org_id,
    count(accounts) AS COUNT,
    ((date_at) - INTERVAL '1 DAY') AS dateat
FROM 
    sourcetable
WHERE 
    date_at <= now() - INTERVAL '130 DAYS'
GROUP BY 
    org_id,
    dateat;

TIPS

Tip 1

You can append multiple operands. E.g.: how to get last day of current month?

SELECT date_trunc('MONTH', CURRENT_DATE) + INTERVAL '1 MONTH - 1 DAY';

Tip 2

You can also create an interval using make_interval function, useful when you need to create it at runtime (not using literals):

SELECT make_interval(days => 10 + 2);
SELECT make_interval(days => 1, hours => 2);
SELECT make_interval(0, 1, 0, 5, 0, 0, 0.0);

More info:

Date/Time Functions and Operators

datatype-datetime (Especial values).

---

Related Topics