date difference

csergiu77 · **Joined:** Fri Mar 11, 2005 11:18 am **Posts:** 8

Sorry if this solution was already asked but i searched and i did not find a proper answer.

I have to create a simple query in sql but i ahve no clue how to make this in HQL :

So i need to find all active customers in from a table that where not active in last 2 months,4 months,6 months.

A backtround task run and has to identify these cusotmers each night and to perfrom an action (email remider,sms etc.)

Customer table has lastTimeLoginDate as timestamp

So i have to calculate currentDate - lastTimeLoginDate each night and if that is 2,4,6 months difference to do something...

How can i do this using HQL ?

Many Thx.

Cris.

alexandrubarbat · **Joined:** Wed Oct 03, 2007 4:10 am **Posts:** 46

You can add an additional property in your Customer mapping

something like

....
<property name="daysOfNonActivity" type="java.lang.Long">
<formula>
<![CDATA[
SYSDATE - LAST_TIME_LOGIN_DATE
]]>
</formula>
</property>
....

and use this property in your HQL. (this formula works in oracle, but there are solutions for other db too)

good luck

jackpunt · **Joined:** Sat Mar 28, 2009 5:47 pm **Posts:** 14

Code:

long curTime = new Date().getTime();
long prevTime = curTime - 30 * 24 * 60 * 60 * 1000; // 30 days ago.
Date prevDate = new Date(prevTime);

String hql "from X where lastTimeLoginDate() < :prevDate";
session.create(hql).setParameter("prevDate", prevDate);