How can sorting objects into buckets be formulated as valid JPQL expression?

Question

I would like to display a 2D-chart containing the amount of processed objects (mapped in JPA) in a given time span. This amount can be scale up to around 30k objects in an timespan of 30 minutes.

As a DBMS I am using PostgreSQL 9.4 and JPA 2.0 with Hibernate in version 4.2.21.

At the moment I am using this piece of code in order to add the amount into my temporal "buckets".

 // Get all objects after a given start date
List<MyObject> myObjects= myJPADbService.getMyObjects(Date.from(startDate.atZone
            (ZoneId.systemDefault()).toInstant()), Integer.MAX_VALUE);

for (MyObject information : myObjects) {
        LocalDateTime pageDate = LocalDateTime.ofInstant(Instant.ofEpochMilli(information.getLastSeen().getTime()), ZoneId.systemDefault());          
    long duration;
    if (temporalUnit == ChronoUnit.MINUTES) {
     duration = Duration.between(startDate, pageDate).toMinutes();
    } else if (temporalUnit == ChronoUnit.DAYS) {
     duration = Duration.between(startDate, pageDate).toDays();
    } else {
     duration = Duration.between(startDate, pageDate).toHours();
   }

  pagesPerUnit[(int) (Math.abs(duration))]++;
}

However depending on an user selected timespan, this piece of code might be very inefficient. It might be more efficient, if the DBMS computes the buckets according to an user selected timespan.

How can this be formulated in proper JPQL syntax?

score 2 · Accepted Answer · edited Jan 04 '16 at 00:33

First of all, you need a JPA way of computing the difference between the startDate and pageDate in the respective unit, which is somewhat hard to formulate because it's very database dependent. In the end you will need some custom functions or write a very complicated JPQL query.

Computing the days between two dates in PostgreSQL is as easy as doing date_part('day', t2 - t1). For the hours you already need date_part('day', t2 - t1) * 24 + date_part('hour', t2 - t1) and minutes date_part('day', t2 - t1) * 24 * 60 + date_part('hour', t2 - t1) * 60 + date_part('minute', t2 - t1).

In order to use these database functions in JPQL, you can use the FUNCTION syntax like FUNCTION('date_part', 'day', :startDate - pageDate).

In the end, you will be grouping by such an expression and do a count by id, something like the following

SELECT COUNT(o.id) FROM MyObject o GROUP BY FUNCTION('date_part', 'day', :startDate - o.pageDate)

How can sorting objects into buckets be formulated as valid JPQL expression?

1 Answers1

Linked