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

Dev-Bookmarks Logo

Save up to a workweek a year by efficiently managing your dev bookmarks, on Share your favorites with the community and they will be 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?

    A cleaner multi-stage continuous deployment on Kubernetes of a Create React App with kustomize, helm and skaffold

    This blog posts presents a clean way to make a multi-stage deployment of a Create React App on a Kubernetes Cluster. Deployments with kubectl, helm charts, kustomize and skaffold are presented. Continue reading