How to add minutes column to Instant value in jOOQ

Given a table like this:

CREATE TABLE demo (
    id BIGINT PRIMARY KEY,
    offset_minutes INTEGER NOT NULL,
    scheduled_time TIMESTAMP WITH TIME ZONE
);

I want to update scheduled_time to an application-supplied time plus offset_minutes. In plain SQL on PostgreSQL, it'd look something like

UPDATE demo
SET scheduled_time =
    timestamp with time zone '2022-09-12T01:23:45Z' +
        offset_minutes * interval '1 minute'
WHERE id = 12345;

What's the best way to express this in jOOQ in a database-engine-independent way?

This is the reverse of the usual way people want to add minutes to a time value, which is well-covered by other SO questions: in my case, the Instant is supplied by the code and the number of minutes is in a database column, not the other way around.

The best I've been able to come up with is to do a floating-point calculation on the number of minutes, since we can add days to Instants:

dslContext.update(DEMO)
    .set(DEMO.SCHEDULED_TIME,
        DSL.instant(instant).add(DEMO.OFFSET_MINUTES.div(24.0 * 60.0)))
    .where(DEMO.ID.eq(id))
    .execute();

On PostgreSQL, jOOQ ends up generating a SQL expression for the value of the SET clause:

(timestamp with time zone '2022-09-12 01:23:45+00:00' +
    ("public"."demo"."offset_minutes" / 1.44E3) * interval '1 day')

The fact that jOOQ is generating interval '1 day' makes me hopeful that there's a way to tell it to change day to minute and avoid the floating-point calculation. I'm never nuts about doing floating-point calculations on discrete quantities if it can be avoided.

Solution

Pending feature requests

Fixing your immediate problem

There's a pending feature request #6723 to add support for DSL::offsetDateTimeAdd, which would allow for adding intervals to TIMESTAMP WITH TIME ZONE data types.

It still wouldn't work for Instant data types, where we'd need yet another "overload" for all the possible date time arithmetic variants.

Both of these would produce a lot of new methods in an already crowded DSL class, without really adding much new functionality, given they're doing something similar to the existing timestampAdd() or localDateTimeAdd() functions, just offering the same thing for new types.

A more strategic, thorough change

There's a big change #11088 suggested on the long term roadmap, which would allow for more cleanly separating the usual two types T and U involved with a Field:

T being the "JDBC type", i.e. the type the database understands (e.g. OffsetDateTime)
U being the "user type", i.e. the type you want to see in your code (e.g. Instant)

This way, there would be a single method accepting T = OffsetDateTime and any arbitrary U type (e.g. Instant).

Workaround for you right now

While the above tasks take time to get right, you can always use the usual escape hatch and use plain SQL templating. E.g.

DSL.field("({0} + {1} * interval '1 minute')", 
    SQLDataType.INSTANT,
    DSL.instant(instant), 
    DEMO.OFFSET_MINUTES
);

You can extract the hard-coded arguments (DSL.instant(instant) and DEMO.OFFSET_MINUTES) and make a reusable function for the above, and thus create a mini library for the missing functionality in jOOQ.