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

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);
    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

    Podcastpedia image

    Adrian Matei

    Creator of Podcastpedia.org and Codingpedia.org, 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?
    F

    How to redirect domain to www url with nginx

    Snippet from nginx config file that redirects all requests (http and https) to the www URL Continue reading