How to update one time column based on another in jOOQ

user_1369677 0
Viewed 284

Let's assume a super simple use-case: Imagine, I have a table (dummyTable) with these columns:

id
dueAt
createdAt

How do I perform a bulk update like the one below using jOOQ

update dummyTable
set dueAt = TIMESTAMPADD(MINUTE, 5, createdAt) 
where ids in (...)

I see examples online that uses hardcoded and supplied values - https://www.jooq.org/doc/3.13/manual/sql-building/column-expressions/datetime-functions/timestampadd-function/. But I couldn't figure out how to get the TIMESTAMPADD function work with existing column values.

What should I have it in the blank?

return rwContext
        .update(DUMMYTABLE)
        .set(DUMMYTABLE.DUEAT, ______________ )
        .where(DUMMYTABLE.ID.in(someIds))
        .execute();

I tried the below code in the blank but it gives a compilation error

timestampAdd(Timestamp.valueOf(DUMMYTABLE.CREATEDAT), 5, DatePart.MINUTE)

Any help or guidance is appreciated.

jooq java
1 Answers

Timestamp.valueOf(String) is JDBC API to create a Timestamp value from a string literal, e.g.

Timestamp.valueOf("2000-01-01 00:00:00.0");

You cannot pass a jOOQ Field<Timestamp> to it. But why would you? Just pass the field reference to the function directly:

timestampAdd(DUMMYTABLE.CREATEDAT, 5, DatePart.MINUTE);

Or, if using JSR 310 types, DSL.localDateTimeAdd():

localDateTimeAdd(DUMMYTABLE.CREATEDAT, 5, DatePart.MINUTE);
user_5217990
Related