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


Codever Logo

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

    Subscribe to our newsletter for more code resources and news

    Adrian Matei

    Adrian Matei
    Life force expressing itself as a coding capable human being

    How to display an element in full screen in Angular with screenfull.js

    Use screenfull.js to display code snippets in full screen in Angular. Continue reading

    Map enum as string with jpa

    Published on June 18, 2021

    How to set createdAt date with jpa

    Published on June 18, 2021