JOOQ localDateTimeDiff with java.time.Duration's

Question

I have a table with a created-timestamp (timestamp) and a time-to-live (interval).

CREATE TABLE my_object (
   id uuid NOT NULL PRIMARY KEY,
   created timestamp NOT NULL,
   time_to_live interval NOT NULL
);

Now I want to find all objects, where their TTL is over. I tried something like this:

public class MyObjectRepository {

  public Stream<MyObjectDto> fetchExpired() {
    return context
        .selectFrom(MY_OBJECT)
        .where(localDateTimeDiff(currentLocalDateTime(), MY_OBJECT.CREATED)
            .greaterThan(MY_OBJECT.TIME_TO_LIVE))
            // ^- compile-error, no overload accepts TableField<MyObjectRecord, Duration>
        .forUpdate()
        .skipLocked()
        .fetchStreamInto(MyObjectDto.class);
  }
}

Perhaps the big-problem over here is, that I have the TTL forced typed into java.time.Duration. But for clean APIs, I can't change the type to DayToSecond.

<!-- others -->
<forcedType>
   <userType>java.time.Duration</userType>
   <converter>org.jooq.Converter.ofNullable(
      org.jooq.types.YearToSecond.class, Duration.class,
      yearToSecond -> yearToSecond.toDuration(), duration -> org.jooq.types.YearToSecond.valueOf(duration)
       )
   </converter>
   <includeTypes>INTERVAL</includeTypes>
</forcedType>
<!-- others -->

How can I do this in JOOQ?

Answer 1

My solution going a little bit in the direction of Michael Gantmans answer, but I don't get it to work.

So ... I changed the SQL-table a little bit. The new schema looks like this:

CREATE TABLE my_object (
   id uuid NOT NULL PRIMARY KEY,
   created timestamp NOT NULL,
   valid_until timestamp NOT NULL
);

With the new schema, it was very easy to get JOOQ working. The code is simplified to this:

public Stream<MyObjectDto> fetchExpired() {
  return context
      .selectFrom(MY_OBJECT)
      .where(MY_OBJECT.VALID_UNTIL.lessThan(ZonedDateTime.now()))
      .fetchStreamInto(MyObjectDto.class);
}

There are some other advantages in the new schema:

• More performant, because the eol-time must not be recalculated for each run
• More performant², because valid_until can be indexed
• Not breaking the API, because the TTL as Duration can be calculated (valid_until - created) if needed