Fetching a sum of intervals in JOOQ

Question

I am looking for a dialect-independent way to return the sum of interval durations between two TIMESTAMP fields, using a select like:

DSL.sum(DSL.timestampDiff(MY_TABLE.START, MY_TABLE.END))

In JOOQ, sum(...) returns a Field<BigDecimal>. This happens to work in HSQLDB, as it uses a millisecond representation for the INTERVAL. In PostgreSQL, however, this query crashes as there is no automatic conversion from the native INTERVAL type to BigDecimal possible.

I am now looking for a cross-platform way in JOOQ to convert the INTERVAL to (milli)seconds, so I can sum these numbers to a numeric value.

Any suggestions? (Or perhaps there is a more elegant way to solve this puzzle?)

Answer 1

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.*;

More info about CustomField here.

Answer 2

From the lists

select extract ('epoch' from '5 days 5 hours'::interval);