CODEWITHSUNDEEP
jooq
Jacob Casper
Jacob Casper

Reputation: 35

jooq: Add interval to timestamp postgres

I'm trying to bump a timestamptz value further in to the future by a number of interval seconds. Is there a way to massage these types so the jooq will allow me to do so in one statement, or do I just need to get the TriggerRecord and do the calculation in Java code?

Code and attempt follows:

public final TableField<TriggerRecord, Instant> PAUSED_UNTIL = createField(DSL.name("paused_until"), SQLDataType.TIMESTAMPWITHTIMEZONE(6), this, "", new OffsetDateTimeInstantConverter());

public class OffsetDateTimeInstantConverter implements Converter<OffsetDateTime, Instant> {
    private static Instant min;

    public OffsetDateTimeInstantConverter() {
    }

    public Instant from(OffsetDateTime databaseObject) {
        return databaseObject == null ? null : databaseObject.toInstant();
    }

    public OffsetDateTime to(Instant userObject) {
        if (userObject == null) {
            return null;
        } else {
            return userObject.isBefore(min) ? OffsetDateTime.MIN : userObject.atOffset(ZoneOffset.UTC);
        }
    }

    public Class<OffsetDateTime> fromType() {
        return OffsetDateTime.class;
    }

    public Class<Instant> toType() {
        return Instant.class;
    }

    static {
        min = OffsetDateTime.MIN.toInstant();
    }

In one case it errors out

final Long ps = 360;
query = using(configuration)
                .update(TRIGGER)
                .set(TRIGGER.ACTIVE, active)
                .set(TRIGGER.PAUSED_UNTIL,
                     TRIGGER.PAUSED_UNTIL.add(ps))
                .returning()
                .fetch();
ERROR: operator does not exist: timestamp with time zone + timestamp with time zone

And in another attempt errors as

        final var query = using(configuration)
                .update(TRIGGER)
                .set(TRIGGER.ACTIVE, active)
                .set(TRIGGER.PAUSED_UNTIL,
                     TRIGGER.PAUSED_UNTIL
                     .add(val(DayToSecond.valueOf(Duration.ofSeconds(ps)))))
org.jooq.exception.DataTypeException: Cannot convert from +0 00:06:00.000000000 (class org.jooq.types.DayToSecond) to class java.time.OffsetDateTime

update trigger set "paused_until" = ("alert"."trigger"."paused_until" + cast(? as timestamp(6) with time zone))

Upvotes: 1

Views: 590

Answers (1)

Lukas Eder
Lukas Eder

Reputation: 220762

This looks like bug #12036, which has been fixed in jOOQ 3.17.0, 3.16.4, and 3.15.8. The workaround is to use plain SQL templating for this particular expression.

DSL.field("{0} + {1}", 
    TRIGGER.PAUSED_UNTIL.getDataType(),
    TRIGGER.PAUSED_UNTIL, ps
);

Upvotes: 2

Related Questions