Criteria API order by time between two LocalDateTime

Question

Given an entity with two date fields of type LocalDateTime:

class MyEntity {

    private LocalDateTime start;
    private LocalDateTime lastUpdate;

}

I want to write a query with criteria api that orders the result set by the time between these two dates.

/* ... */
Root<MyEntity> root = query.from(MyEntity.class);
Order order = ???
root.orderBy(cb.desc(order));
/* ... */

My ideas so far:

• CriteriaBuilder#diff wants its arguments to be derived from Number, which LocalDateTime doesn't
• CriteriaBuilder#treat maybe treat it as a Timestamp somehow? It is stored as timestamp in the database after all

p.s.:

• using EclipseLink 2.7.3
• looking for a solution that is not tied to a specific database

Answer 1

Your idea of cb.treat(..) that would do a downcast would not work because Number does not extend/inherit LocalDateTime. And even if it would be possible there would then be a problem because there would not be a way to cast database timestamp type to any Number either directly in database side.

I found this question which might help you. Accepted answer makes use of standard(?) SQL function TIMESTAMPDIFF and that should be quite implementation independent solution. You could make some generic sort based on that answer.

However, one option would also be adding a new calculated field for duration / interval like:

private Duration duration;

@PrePersist // update on each persist
private void calcDuration() {
    duration = Duration.between(start, lastUpdate);
}

Then ordering would be like:

Path<Long> duration = root.get("duration");  // duration is stored as bigint or like
criteriaQuery.orderBy(criteriaBuilder.desc(duration)); // or .asc(..)

Naturally duration consumes extra space in db - and it should normally be just transient and/or calculated value - but it might also bring some performance boost.