Fetching a sum of intervals in JOOQ
This is probably a bug in jOOQ (#5785).
As a workaround, you can implement your own sum function like this:
public static Field<BigDecimal> sumTimestampDiff( final Field<Timestamp> start, final Field<Timestamp> end) { return new CustomField<BigDecimal>("sum", SQLDataType.NUMERIC) { @Override public void accept(Context<?> ctx) { switch (ctx.family()) { case HSQLDB: ctx.visit(sum(timestampDiff(start, end))); break; case POSTGRES: ctx.visit(sql("sum(1000 * extract('epoch' from ({0} - {1})))", start, end)); break; } } };}I'm using the extract() expression from Evan's answer here.
The above code snippet assumes you have this static import:
import static org.jooq.impl.DSL.*;