How to add or substract a time period to a date in Java Persistence Query Language – JPQL

(P) Codever is an open source bookmarks and snippets manager for developers & co. See our How To guides to help you get started. Public bookmarks repos on Github ⭐🙏

There are quite often situations, when you’d need to add or substract a time period to a date when you are accessing the database via Java Persistence API(JPA). Now

How To

Let’s see how it can be done. Consider the following scenario: I need to get recent items(podcasts) from the database, let’s say that were inserted in the last 8 days. For that I’ll build a function that looks back into the past by the numberOfDaysToLookBack parameter:

public List<Podcast> getRecentPodcasts(int numberOfDaysToLookBack) {

	Calendar calendar = new GregorianCalendar();
	calendar.setTimeZone(TimeZone.getTimeZone("UTC+1"));//Munich time
	calendar.setTime(new Date());
	calendar.add(Calendar.DATE, -numberOfDaysToLookBack);//substract the number of days to look back
	Date dateToLookBackAfter = calendar.getTime();

	String qlString = "SELECT p FROM Podcast p where p.insertionDate > :dateToLookBackAfter";
	TypedQuery<Podcast> query = entityManager.createQuery(qlString, Podcast.class);
	query.setParameter("dateToLookBackAfter", dateToLookBackAfter, TemporalType.DATE);

	return query.getResultList();
}

and call it like getRecentPodcast(8);

Note that the date calculation takes place in Java. One way to do it is by using the Java Calendar‘s  add function with a negative value.  After that you can use the calculated date as parameter in the JPQL comparison.

If you don’t like the Java calendar approach, you can achieve the same results with Joda-Time:

DateTime dateToLookBackAfterJoda = new DateTime(new Date());
dateToLookBackAfterJoda = dateToLookBackAfterJoda.minusDays(numberOfDaysToLookBack);
dateToLookBackAfterJoda.toDate();

Note: If you want to have an addition instead of substraction use Calendar.add() with a positive value or Joda-Time addDays(int numberOfDays) function.

Check out also my related posts on the topic

• Java Persistence Example with Spring, JPA2 and Hibernate – CRUD example with JPA, Spring and Hibernate incorporating the method from above
• RESTful Web Services Example in Java with Jersey, Spring and MyBatis – defines a REST API that among others, it requests recent podcasts employing the above method