4

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")
                            )
benstpierre
  • 32,833
  • 51
  • 177
  • 288

2 Answers2

4

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")
Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
  • Thanks for the answer! Can I ask how we can use java.time.Instant with timestamp anyways? I see reasons it's not recommended, but sometimes it just fits (when application operates with only utc dates, so we dont need tz conversion). How can one override jooq behaviour? im trying to use ``` ForcedType() .withName("INSTANT") .withTypes("TIMESTAMP") ``` It generates Instant java type, but does casts to timestamptz anyways. How we can tweak this built-in binding? – Dmitry Apr 19 '22 at 20:32
  • 1
    @Dmitry: [You can always implement your own data type binding](https://www.jooq.org/doc/latest/manual/code-generation/codegen-advanced/codegen-config-database/codegen-database-forced-types/codegen-database-forced-types-binding/) – Lukas Eder Apr 19 '22 at 20:53
1

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

Dmitry
  • 330
  • 1
  • 14