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

Dev-Bookmarks Logo

(P) - Open source Bookmarks and Codelets Manager for Developers & Co. See our How To guides to help you get started. Share your favorites bookmarks with the community and they might get published on Github - Star

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

  • the bad news is that Java Persistence Query Language(JPQL) does not support such operations on dates yet.
  • the good news is that it is possible by using a native query or doing the computation on the Java side. I prefer the second option as it provides database independence.
  • 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);

    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

    Podcastpedia image

    Adrian Matei

    Creator of and, computer science engineer, husband, father, curious and passionate about science, computers, software, education, economics, social equity, philosophy - but these are just outside labels and not that important, deep inside we are all just consciousness, right?
    Subscribe to our newsletter for more code resources and news

    My troubles getting to run a Node application with PM2 in cluster mode with NVM

    In this blog post I will list of the trouble I went through getting to run PM2 in cluster mode on an Ubuntu system, on which I had Node.js both standalone installed and managed with NVM. Continue reading