In JOOQ how do we use Java Instant to map to Postgresql's timetamp type?

Question

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

Answer 1

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

Answer 2

Gladly Lukas Eder gave as an almost complete hint to a solution using converters from docs - You can always implement your own data type binding

We need to define a Converter

class JooqInstantConverter: Converter<LocalDateTime, Instant> {

  override fun to(databaseObject: Instant?): LocalDateTime? {
    if (databaseObject == null)
      return null
    return LocalDateTime.ofInstant(databaseObject, ZoneOffset.UTC)
  }

  override fun from(userObject: LocalDateTime?): Instant? {
    return userObject?.toInstant(ZoneOffset.UTC)
  }

  override fun toType(): Class<Instant> {
    return Instant::class.java
  }

  override fun fromType(): Class<LocalDateTime> {
    return LocalDateTime::class.java
  }
}

And a little tweak in codegen

ForcedType()
  .withUserType("java.time.Instant")
  .withConverter("xxx.JooqInstantConverter")
  .withTypes("TIMESTAMP")

One can use this thoughtfully, with an original answer in mind