[SOLVED] In jOOQ how do we use Java Instant to map to Postgresql's timestamp type?

In jOOQ how do we use Java Instant to map to Postgresql's timestamp type?

I was rather surprised to find out that jOOQ (as of 3.16) binds timestamp to LocalDateTime. In my mind timestamp is most naturally mapped to an Instant, it is a Unix epoch timestamp.

So how can we get jOOQ to generate code that uses Instant instead of LocalDateTime? Do I need to use a force generator?

I tried using forced type like this but it never picks up my forced type.

.withForcedTypes(
    new ForcedType()
        .withUserType(Instant.class.getCanonicalName())
        .withConverter(com.monsolei.data.InstantConverter.class.toString())
        .withIncludeExpression("timestamp")
)

Solution

On PostgreSQL timestamp data types

In PostgreSQL (I'm assuming you're using PG), TIMESTAMP is short for TIMESTAMP WITHOUT TIME ZONE as documented here: https://www.postgresql.org/docs/current/datatype-datetime.html

The best Java types to map that to are:

java.sql.Timestamp (the old JDBC type, whose valueOf() and toString() behaviours do not support time zones and also work with your local time zone)
java.time.LocalDateTime

You can see this also in the JDBC spec, or derive it from the fact that there are methods like java.sql.Timestamp.valueOf(LocalDateTime) and java.sql.Timestamp.toLocalDateTime().

The fact that you prefer working with java.time.Instant hints at it being better to use TIMESTAMP WITH TIME ZONE in PostgreSQL, which JDBC (and thus jOOQ) maps to OffsetDateTime by default. You can rewrite that type without a converter to INSTANT in jOOQ: https://www.jooq.org/doc/latest/manual/code-generation/codegen-advanced/codegen-config-database/codegen-database-forced-types/

You can also see this recommendation in the PostgreSQL wiki: https://wiki.postgresql.org/wiki/Don't_Do_This#Don.27t_use_timestamp_.28without_time_zone.29

Your specific configuration

Regarding your specific attempts to map the type:

.withIncludeExpression("timestamp")

That only applies to column named timestamp. You probably meant to apply your forced type to type names?

.withIncludeTypes("timestamp")