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


Dev-Bookmarks Logo

(P) Bookmarks.dev - Open source Bookmarks and Code 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 filter results list in Angular with input text

    Real life example showing how to filter results list in Angular and its implementation with Angular pipes. Continue reading

    GraphQL Resources to help you get started

    Published on November 05, 2020