java Jooq日至秒平均值

Question

我正在尝试使用Jooq在嵌套请求中执行平均时间戳字段。我几乎完成了，除了Jooq无法将DayToSecond类型转换为BigDecimal之外，因为它的转换基于DayToSecond字符串表示，并且DayToSecond的表示形式如下所示：

00:00:55.646

因此转换无法完成并引发异常

我使用了JOOQ3.11.2

这是表格：

CREATE TABLE IF NOT EXISTS MISSION (
  ID         SERIAL PRIMARY KEY,
  START_DATE TIMESTAMP,
  SOLVE_DATE TIMESTAMP);

这就是JOOQ请求：

Field<DayToSecond> SPEED = field("SPEED", DayToSecond.class);
Table<Record1<DayToSecond>> nested =
        context.select(
                timestampDiff(Tables.MISSION.SOLVE_DATE,
                              Tables.MISSION.START_DATE)
                                .as(SPEED))
                        .from(Tables.MISSION_USER)
                        .asTable();
context.select(
        avg(nested.field(SPEED)).as("avg"), // Error with this line
        max(nested.field(SPEED)).as("max"),
        min(nested.field(SPEED)).as("min"))
    .from(nested)
    .fetchOne().into(Efficiency.class);

效率类是一个POJO，有3个整型字段（平均值、最大值、最小值）

以下是stacktrace：

    Request processing failed; nested exception is org.springframework.jdbc.UncategorizedSQLException: jOOQ; uncategorized SQLException for SQL [select avg("alias_89583003"."SPEED") as "avg", max("alias_89583003"."SPEED") as "max", min("alias_89583003"."SPEED") as "min" from (select ("public"."mission"."solve_date" - "public"."mission"."start_date") as "SPEED" from "public"."mission") as "alias_89583003"]; SQL state [null]; error code [0]; Error while reading field: "avg", at JDBC index: 1; nested exception is java.sql.SQLException: Error while reading field: "avg", at JDBC index: 1] with root cause
org.postgresql.util.PSQLException: Mauvaise valeur pour le type BigDecimal : 00:00:55.646
    at org.postgresql.jdbc.PgResultSet.toBigDecimal(PgResultSet.java:2885)
    at org.postgresql.jdbc.PgResultSet.toBigDecimal(PgResultSet.java:2894)
    at org.postgresql.jdbc.PgResultSet.getBigDecimal(PgResultSet.java:2353)
    at org.postgresql.jdbc.PgResultSet.getBigDecimal(PgResultSet.java:396)
    at org.jooq.tools.jdbc.DefaultResultSet.getBigDecimal(DefaultResultSet.java:323)
    at org.jooq.impl.CursorImpl$CursorResultSet.getBigDecimal(CursorImpl.java:695)
    at org.jooq.impl.DefaultBinding$DefaultBigDecimalBinding.get0(DefaultBinding.java:1363)
    at org.jooq.impl.DefaultBinding$DefaultBigDecimalBinding.get0(DefaultBinding.java:1326)
    at org.jooq.impl.DefaultBinding$AbstractBinding.get(DefaultBinding.java:774)
    at org.jooq.impl.CursorImpl$CursorIterator$CursorRecordInitialiser.setValue(CursorImpl.java:1720)
    at org.jooq.impl.CursorImpl$CursorIterator$CursorRecordInitialiser.operate(CursorImpl.java:1689)
    at org.jooq.impl.CursorImpl$CursorIterator$CursorRecordInitialiser.operate(CursorImpl.java:1654)
    at org.jooq.impl.RecordDelegate.operate(RecordDelegate.java:125)
    at org.jooq.impl.CursorImpl$CursorIterator.fetchNext(CursorImpl.java:1618)
    at org.jooq.impl.CursorImpl$CursorIterator.hasNext(CursorImpl.java:1585)
    at org.jooq.impl.CursorImpl.fetchNext(CursorImpl.java:407)
    at org.jooq.impl.Tools.fetchOne(Tools.java:1762)
    at org.jooq.impl.AbstractResultQuery.fetchOne(AbstractResultQuery.java:545)
    at org.jooq.impl.SelectImpl.fetchOne(SelectImpl.java:2879)

那么，有没有一种解决方案可以在DayToSecond上执行平均值呢

Answer 1

通过从间隔差中提取秒数，并在Java中将其作为一个普通数，可以完全避免DayToSecond：

使用此函数（部分取自here）：

public static Field<Integer> diff(Field<Instant> field1, Field<Instant> field2) {
    return DSL.field("extract(epoch from {0} - {1})", Integer.class, field1, field2);
}

要改变这一点：

timestampDiff(Tables.MISSION.SOLVE_DATE, Tables.MISSION.START_DATE)

对此：

diff(Tables.MISSION.SOLVE_DATE, Tables.MISSION.START_DATE)

然后将SPEED声明为：

Field<Integer> SPEED = field("SPEED", Integer.class);