Using Hibernate HQL to select records where two dates are within a specified interval

Question

I'm trying to use HQL to find records where two date fields are within 1 month of each other.

Query query = session.createQuery("from com.ep.cqprojects.db.Projects "
    + "where active_date - kickoff_meeting_date < interval '1' month ");

Unfortunately the database used apparently doesn't understand interval.

How can I compare the interval between two date fields?

Answer 1

Does it have to be a calendar month? I'm afraid there's no good HQL-only solution in that case. The closest thing you can get is:

from com.ep.cqprojects.db.Projects
 where active_date - kickoff_meeting_date < 31
   and month(active_date) - month(kickoff_meeting_date) < 2

month() is ANSI SQL functions and so hopefully should be supported by your database engine (incidentally, what database is that?) Second condition is needed to weed out cases where active_date is last day of previous month and kickoff_meeting_date is first day of next month with month in between them less than 31 days long.
You can further extend this to handle time if you need to. Ultimately, however, you may be better off getting approximate results and further re-checking / filtering them in your code.