Having recently felt compelled to start using tstzrange to represent time intervals in Postgres (instead of two separate start and end columns), I’ve also had to grapple with writing a custom Scala class for Jooq to know what kind of code to generate to represent the column.

This article is split into two parts. The first part talks about writing such a class to represent Postgres’s tstzrange column. The second part talks about how to use that custom class with Jooq.

Why range types?

Range types offers better expressivity in the form of specialized operators. && checks for the intersection between two ranges, >> checks if one range is strictly on the right of the other, -|- checks if two ranges are adjacent. The full list of operators can be found here.

Range types have built-in validation to make sure that the start cannot be greater than the end.

Range types can express the concept of inclusive and exclusive bounds. [3,4]::int4range includes both 3 and 4, [3,4)::int4range includes only 3.

Range types can be indexed multidimensionally using GiST indexes, and can also optionally combined with B-Tree indexes for queries involving both range type columns and scalar columns.

ALTER TABLE reservation ADD EXCLUDE USING GIST (room_id WITH =, booked_time WITH &&);

This example constraint maintains the invariant that no room can be double-booked.

Discrete vs continuous ranges

Ranges can be dichotomized into discrete ranges and continuous ranges. Discrete ranges are ranges like int8range, whose element type have well-defined steps (in this case, the set of all 8-bit integers). Continuous ranges are ranges like tstzrange and floatrange, where the element type does not have well-defined steps.

Discrete ranges can be canonicalized into a [x,y) form, since for any x, we can identify its predecessor and successor values. Furthermore, in discrete ranges, there exists the edge case of (x,x + delta) being equivalent to an empty interval, e.g. (3,4)::int4range. In continuous ranges, the only valid empty interval representations are (x,x), [x,x), and (x,x] .

Representing `tstzrange` in Scala

With some of the context above, we can now dive into the implementation proper.

One of the most important methods to have on a potential tstzrange class is overlaps, and as you will see, implementing overlaps as a primitive will enable us to trivially implement contains (and a host of other methods).

We need to handle five cases here.

Given two ranges A and B:

Either A or B is an empty interval
Either A or B is an infinite interval ((,))
Either A or B is an unbounded interval ((x,) or (,x))
Both A and B are unbounded intervals
Both A and B are bounded intervals

This looks complicated because we need to handle unbounded intervals and inclusive/exclusive ranges (and since tstzrange is not a discrete range, we cannot canonicalize it beforehand).

I’ve chosen to enforce the “start cannot greater than end” validation using a simple Scala requires, which will throw a runtime IllegalArgumentException. You may choose to override the apply function with one that returns an Option[TsTzRange] instead, if so desired.

import java.time.Instant
 
import scala.math.Ordering.Implicits._
 
case class TsTzRange(
  start: Option[Instant],
  end: Option[Instant],
  startInclusive: Boolean = true,
  endInclusive: Boolean = true) {
 
  // if start and end are specified, start must be less than or equal to end
  require((for { s <- start; e <- end} yield { s <= e}).getOrElse(true))
 
  def zero: Boolean = this.bounded && this.start == this.end && !this.startInclusive || !this.endInclusive
 
  def infinite: Boolean = this.start.isEmpty && this.end.isEmpty
 
  // unbounded in this context refers to an interval that is only unbounded on one side
  def unbounded: Boolean = leftUnbounded || rightUnbounded
 
  def leftUnbounded: Boolean = this.start.isEmpty && this.end.nonEmpty
 
  def rightUnbounded: Boolean = this.start.nonEmpty && this.end.isEmpty
 
  def bounded: Boolean = this.start.nonEmpty && this.end.nonEmpty
 
  private def touchingEdges(left: TsTzRange, right: TsTzRange): Boolean =
    left.end == right.start && left.endInclusive && right.startInclusive
 
  // Checks if the end of bounded2 lies anywhere within bounded1
  private def boundedCheckLeftOverlap(bounded1: TsTzRange, bounded2: TsTzRange): Boolean =
    bounded2.end > bounded1.start && bounded2.end <= bounded1.end
 
  // Checks if the start of bounded2 lies anywhere within bounded1
  private def boundedCheckRightOverlap(bounded1: TsTzRange, bounded2: TsTzRange): Boolean =
    bounded2.start >= bounded1.start && bounded2.start < bounded1.end
 
  // boundedCheck assumes both intervals are bounded
  private def boundedCheck(bounded1: TsTzRange, bounded2: TsTzRange): Boolean =
    boundedCheckLeftOverlap(bounded1, bounded2) ||
      boundedCheckRightOverlap(bounded1, bounded2) ||
      touchingEdges(bounded2, bounded1) ||
      touchingEdges(bounded1, bounded2)
 
  // checks if both ranges are unbounded in opposite directions, and check overlap if so
  private def oppositeUnboundedCheck(leftUnbounded: TsTzRange, rightUnbounded: TsTzRange): Boolean =
    leftUnbounded.leftUnbounded &&
      rightUnbounded.rightUnbounded &&
      (leftUnbounded.end > rightUnbounded.start || touchingEdges(leftUnbounded, rightUnbounded))
 
  // checks if both ranges are unbounded in the same direction
  private def sameUnboundedCheck(unbounded1: TsTzRange, unbounded2: TsTzRange): Boolean =
    (unbounded1.leftUnbounded && unbounded2.leftUnbounded) ||
      (unbounded1.rightUnbounded && unbounded2.rightUnbounded)
 
  private def doubleUnboundedCheck(unbounded1: TsTzRange, unbounded2: TsTzRange): Boolean =
    sameUnboundedCheck(unbounded1, unbounded2) ||
      oppositeUnboundedCheck(unbounded1, unbounded2) ||
      oppositeUnboundedCheck(unbounded2, unbounded1)
 
  private def singleUnboundedLeftUnboundedCheck(bounded: TsTzRange, leftUnbounded: TsTzRange): Boolean =
    leftUnbounded.leftUnbounded && leftUnbounded.end > bounded.start
 
  private def singleUnboundedRightUnboundedCheck(bounded: TsTzRange, rightUnbounded: TsTzRange): Boolean =
    rightUnbounded.rightUnbounded && rightUnbounded.start < bounded.end
 
  // if only one of the intervals is bounded
  private def singleUnboundedCheck(bounded: TsTzRange, unbounded: TsTzRange): Boolean =
    singleUnboundedLeftUnboundedCheck(bounded, unbounded) ||
      singleUnboundedRightUnboundedCheck(bounded, unbounded) ||
      touchingEdges(bounded, unbounded) ||
      touchingEdges(unbounded, bounded)
 
  def overlaps(other: TsTzRange): Boolean = {
    // if any of the intervals are zero, return false early
    !(this.zero || other.zero) &&
      // if any of the intervals are infinite, return true early
      (this.infinite || other.infinite) ||
      // otherwise, depending on the boundedness of the 2 intervals, check accordingly
      (this.unbounded && other.unbounded && doubleUnboundedCheck(this, other)) ||
      (!this.unbounded && !other.unbounded && boundedCheck(this, other)) ||
      (!this.unbounded && other.unbounded && singleUnboundedCheck(this, other)) ||
      (this.unbounded && !other.unbounded && singleUnboundedCheck(other, this))
  }
 
  def contains(other: Instant): Boolean = {
    overlaps(TsTzRange(Some(other), Some(other)))
  }
 
  // returns true if it overlaps with the current time
  def isCurrent: Boolean = {
    this.contains(Instant.now)
  }
}

Property testing `tstzrange`

Property testing is fun because it’s a chance to think outside the box. It is also a rare example of a programming technique that approaches mathematics in conciseness and rigour. Property testing achieves this by delegating all of the heavy lifting to the testing library, which will generate all sorts of test cases to “stress-test” your code.

We can posit properties like these:

Given any three timestamps f1, f2, and f3, if [f1,f2] contains f3, then f3 >= f1 and f3 <= f2. Else, f3 < f1 or f3 > f2.

Given any two timestamps f1 and f2, if (,f1) overlaps (f2,), then f1 > f2. Else, f1 <= f2.

And they can be implemented like so (note the usage of the scalacheck-toolbox library to assist in generating timestamps):

import scala.math.Ordering.Implicits._
import com.fortysevendeg.scalacheck.datetime.instances.jdk8._
import com.fortysevendeg.scalacheck.datetime.jdk8.GenJdk8.genZonedDateTime
import org.scalatest.flatspec.AnyFlatSpec
import org.scalatest.matchers.should.Matchers
import org.scalatestplus.scalacheck.ScalaCheckDrivenPropertyChecks
 
class TsTzRangeSpec extends AnyFlatSpec with Matchers with ScalaCheckDrivenPropertyChecks {
  "TsTzRange's contains" should "work correctly" in {
    forAll(genZonedDateTime, genZonedDateTime, genZonedDateTime) {(f1, f2, f3) =>
      if (f1 <= f2) {
        if (TsTzRange(Some(f1.toInstant), Some(f2.toInstant), false, false).contains(f3.toInstant)) {
          f1 should be < f3
          f3 should be < f2
        } else {
          f3 should (be <= f1 or be >= f2)
        }
      }
    }
  }
  "TsTzRange" should "handle unbounded ranges correctly" in {
    forAll(genZonedDateTime, genZonedDateTime) {(f1, f2) => {
      if (TsTzRange(None, Some(f1.toInstant), false, false).overlaps(TsTzRange(Some(f2.toInstant), None, false, false))) {
        f1 should be > f2
      } else {
        f1 should be <= f2
      }
    }}
  }
}

In the next post, we’ll see how to hook this Scala class up to Jooq.

Time Intervals in Scala

#Why range types?

#Discrete vs continuous ranges

#Representing tstzrange in Scala

#Property testing tstzrange

Why range types?

Discrete vs continuous ranges

Representing `tstzrange` in Scala

Property testing `tstzrange`