CODEWITHSUNDEEP
hibernatetimestamphqldateintervals
Richard
Richard

Reputation: 184

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

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?

Upvotes: 1

Views: 6241

Answers (2)

Darrell Teague
Darrell Teague

Reputation: 4282

Alas, JPQL is not quite there yet. To compare two date fields accurately will require a query to bring the entities in question into date (DATE or TIMESTAMP) Java/JPA object types and then compare them (with the recommendation being to use the Java Calendar or JodaTime) calendar classes. Once there, subtracting one month from one of the calendars and then comparing the difference (in days, hours, etc) between the two will yield an accurate result. See Java Calendar.add() and Calendar.before() or after() methods.

Upvotes: 0

ChssPly76
ChssPly76

Reputation: 100706

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.

Upvotes: 2

Related Questions