CODEWITHSUNDEEP
postgresqljooq
DWilches
DWilches

Reputation: 23035

How to subtract Instants and compare with a Duration in jOOQ

I'm trying to write this query with jOOQ for Postgres:

SELECT * FROM my_table t WHERE t.instant1 - t.instant2 > interval '1 days';

So I wrote this:

Duration oneDay = Duration.ofDays(1);
using(configuration)
    .selectFrom(MY_TABLE)
    .where(MY_TABLE.INSTANT1.minus(MY_TABLE.INSTANT2).gt(oneDay))

But it doesn't compile because of:

Cannot resolve method 'lt(java.time.Duration)'

I tried wrapping oneDay in a val() and then the error changed to:

Cannot resolve method 'lt(org.jooq.Param)'

I think the issue is the method minus returns a Field<Instant> instead of a Field<Duration>, but in Postgres, when subtracting 2 TIMESTAMP WITH TIME ZONE the result is an interval.

Along these lines, jOOQ even allowed me to write this query that would fail at runtime as the types won't line up:

using(configuration)
    .selectFrom(MY_TABLE)
    .where(MY_TABLE.INSTANT1.minus(MY_TABLE.INSTANT2).gt(Instant.now()))

Is there any other jOOQ method I can use? besides doing something like:

"{0} - {1} > interval '1 day'"

Thanks.

Upvotes: 2

Views: 1345

Answers (1)

Lukas Eder
Lukas Eder

Reputation: 221076

jOOQ 3.12 still has a few limitations with respect to interval and interval arithmetic support. The best approach I can think of here is to change your expression:

t.instant1 - t.instant2 > interval '1 days'

To the equivalent

t.instant1 > t.instant2 + interval '1 days'

Which is easy to represent in jOOQ:

T.INSTANT1.gt(T.INSTANT2.plus(1))

Upvotes: 2

Related Questions