Jooq average on DayToSecond

Question

I am trying to perform an average on timestamp field in nested request with Jooq. I am almost done except that Jooq cannot convert DayToSecond type to BigDecimal because it bases its conversion on DayToSecond string representation and a representation of a DayToSecond looks like:

00:00:55.646

So the conversion cannot be completed and an exception is thrown.

I used JOOQ 3.11.2

That is the table:

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

And that is the JOOQ request:

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

The Efficiency class is a POJO with 3 int fields (avg, max, min).

Here is the 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)

So, is there a solution to perform average on DayToSecond?

The Exception shows you have two fields called `"avg"` in your query, though in your code we see there is only one. Could you post the exact exception you get with the current version of your code? I'm referring to this piece of the text: `select avg("alias_89583003"."SPEED") as "avg", (sum("alias_89583003"."SPEED") / count("alias_89583003"."SPEED")) as "avg"`. Also, can you show the declaration for the class `DayToSecond`? — Daniel, Jul 13 '18 at 16:02
@MondKin The `DayToSecond` is a Jooq class. My bad for the double `"avg"`, I was the bad exception message... I edit it! — Kruschenstein, Jul 13 '18 at 16:05

Daniel · Accepted Answer · 2018-07-13T17:21:05.220

You can avoid DayToSecond altogether by extracting the number of seconds from your interval difference, and getting it in Java as a plain number:

Use this function (partially taken from here):

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

For changing this:

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

Into this:

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

Then you declare SPEED as:

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

Jooq average on DayToSecond

1 Answers1