postgresql - 在 JOOQ 中获取间隔总和

Question

我正在寻找一种与方言无关的方法来返回两个 TIMESTAMP 字段之间的间隔持续时间之和，使用如下选择:

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

在 JOOQ 中，sum(...)返回 Field<BigDecimal> .这恰好适用于 HSQLDB，因为它使用 INTERVAL 的毫秒表示。但是，在 PostgreSQL 中，此查询会崩溃，因为无法从 native INTERVAL 类型自动转换为 BigDecimal。

我现在正在 JOOQ 中寻找一种跨平台的方式来将 INTERVAL 转换为(毫秒)秒，这样我就可以将这些数字相加为一个数值。

有什么建议吗？ (或者也许有更优雅的方法来解决这个难题？)

Answer 1

这可能是 jOOQ 中的错误 (#5785)。

作为解决方法，您可以像这样实现自己的求和函数:

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;
            }
        }
    };
}

我正在使用 the extract() expression from Evan's answer here .

上面的代码片段假定您有这个静态导入:

import static org.jooq.impl.DSL.*;

More info about CustomField here .