Custom Jooq Types

September 07, 2020

This is the second part of the two-part series on writ­ing a custom Scala class to rep­re­sent the tstzrange column type in Post­gres. Find the first part here.

Here I’m plac­ing this bind­ing class in the same file as the tstzrange case class:

import java.sql.{SQLFeatureNotSupportedException, Types}
import java.time.{Instant, ZonedDateTime}
import java.util.Objects

import org.jooq._
import org.jooq.impl.DSL

class PostgresTimestampTimezoneRangeBinding extends Binding[Object, TsTzRange] {
  // This regex captures start and end bounds (as either closed or open) and the two values inside
  // We use a lazy quantifier to avoid capturing beyond the next match
  private val rangeRegex = raw"([\[\(])(.*?),(.*?)([\]\)])".r
  override def converter: Converter[Object, TsTzRange] = new Converter[Object, TsTzRange]() {
    override def from(t: Object): TsTzRange =
      if (t == null) null
      else {
        t.toString match {
          case rangeRegex(startBound, start, end, endBound) =>
            val startInstant: Option[Instant] =
              if (start == "") None
              else Some(ZonedDateTime.parse(start.replace("\"", "").replace(" ", "T") + ":00").toInstant)
            val endInstant: Option[Instant] =
              if (end == "") None
              else Some(ZonedDateTime.parse(end.replace("\"", "").replace(" ", "T") + ":00").toInstant)
            TsTzRange(startInstant, endInstant, startBound == "[", endBound == "]")

    override def to(u: TsTzRange): Object =
      if (u == null) null
      else {
        val startBound = if (u.startInclusive) "[" else "("
        val endBound = if (u.endInclusive) "]" else ")"
        val start ="")
        val end ="")

    override def fromType: Class[Object] =

    override def toType: Class[TsTzRange] =

  override def sql(ctx: BindingSQLContext[TsTzRange]): Unit =

  override def register(ctx: BindingRegisterContext[TsTzRange]): Unit =
    ctx.statement.registerOutParameter(ctx.index, Types.OTHER)

  override def set(ctx: BindingSetStatementContext[TsTzRange]): Unit =
    ctx.statement.setString(ctx.index, Objects.toString(ctx.convert[Object](converter).value, null))

  override def get(ctx: BindingGetResultSetContext[TsTzRange]): Unit =

  override def get(ctx: BindingGetStatementContext[TsTzRange]): Unit =

  override def set(ctx: BindingSetSQLOutputContext[TsTzRange]): Unit =
    throw new SQLFeatureNotSupportedException

  override def get(ctx: BindingGetSQLInputContext[TsTzRange]): Unit =
    throw new SQLFeatureNotSupportedException

A note about the parts of the imple­men­ta­tion high­light­ed above.

First, an expla­na­tion of the regex. The Post­gres string rep­re­sen­ta­tion of tstzrange looks some­thing like this: ["2020-08-21 00:00:00+00","2020-09-15 00:00:00+00"). So what we want to do is to cap­ture the open­ing and clos­ing brack­ets, as well as what­ev­er is between the brack­et and the comma sep­a­ra­tor. Note that we don’t match on the lit­er­al double quotes, since single-unbound­ed ranges don’t have them, e.g. ["2020-08-21 00:00:00+00",) I’m sure there is a way to do it with pure regex, but it’s more straight­for­ward to just remove them with code.

In the next 2 high­light­ed lines, we do what seems like a brit­tle string mung­ing code to turn the time­stamp string into a ISO-8601 string that Java likes. The reason I chose to do this is because I want to rely on Java’s built-in parsers. It’s not easy to define a custom format with SimpleDateFormat or DateTimeFormatter because Post­gres time­stamps have vari­able pre­ci­sion.

See Sec­tion 8.5.2 Date/​Time Output in the Post­gres doc­u­men­ta­tion and this Stack Over­flow answer for more back­ground on whether replac­ing the T sep­a­ra­tor with a space is still con­sid­ered ISO-8601/​RFC3339. Regard­less, Java parsers only like time­stamp strings with the T sep­a­ra­tor.

ISO 8601 spec­i­fies the use of upper­case letter T to sep­a­rate the date and time. Post­greSQL accepts that format on input, but on output it uses a space rather than T, as shown above. This is for read­abil­i­ty and for con­sis­ten­cy with RFC 3339 as well as some other data­base sys­tems.

See also the Java doc­u­men­ta­tion here. Java only accepts time­zone off­sets with hh:mm, not just hh. In my opin­ion, we don’t have to worry about double append­ing the :00 por­tion, since Post­gres nor­mal­izes all time­stamps to UTC on write.

This is an exam­ple of how the trans­for­ma­tion works:

"2020-01-01 00:00:00.000+08"
2020-01-01 00:00:00.000+08

And final­ly, in your build.scala, add the high­light­ed lines to your exist­ing Jooq gen­er­a­tion config:

object Settings {
  lazy val jooqGen: Seq[Def.Setting[_]] = Seq(
    libraryDependencies ++= Seq(
      "org.postgresql" % "postgresql" % Versions.postgres % "jooq"
    jooqVersion := Versions.jooq,
    jooqCodegenConfig :=
        System.getenv().getOrDefault("JDBC_DATABASE_URL", s"jdbc:postgresql://localhost:5432/mydb?user=postgres")
      sources in doc in Compile := Seq.empty
  ) ++
    Defaults.packageTaskSettings(packageSrc, ++ common

Final­ly, this can be used like so:

val results = dsl
println(results.head.appointmentTime) // TsTzRange(Some(2020-08-19T08:12:56.320Z),true,Some(2021-08-19T00:14:36.320Z),true)

    TsTzRange(Some(, true, Some(, true)