Postgresql – How to pass a custom usertype as a parameter to a Spring Data JPA query method?

I have a Spring data JPA repository which is backed by a postgresql database. As part of the query I want to use an interval in the query. I managed to achieve it by creating an SQLFunction that casts the string like this:

  public String render(Type firstArgumentType, List args, SessionFactoryImplementor factory)
      throws QueryException {
    return "cast(" + args.get(0) + " as interval)";
  }

It works, but in reality it does not translate to an interval type but a string (a cstring) which is then recast by postgresql to an interval. While this works, it has some undesirable implications when it is being passed to another SQL function (for example in some cases a server-side prepared statement parameter that is expected to be an interval but is a cstring ends up being inefficient in large queries).

One such example is the time_bucket function by the timescaledb extension, which expects an interval as its first argument. I added the function to a class that extends PostgisPG95Dialect as follows:

this.registerFunction("time_bucket", new StandardSQLFunction("time_bucket", new OffsetDateTimeType()));

But when I try to pass its argument, it is never passed as an interval, irrespective of whether I use string syntax as above, or a Duration mapped as shown below.

I know that the postgresql driver has the PGInterval type which is supposed to achieve this. However, I can’t get it to work with Spring Data JPA parameters.

I tried to pass PGInterval directly but instead it gets translated to a bytea.

I tried to use a Converter with the Duration class, but it doesn’t seem to be invoked and the parameter is sent as bigint instead.

@Converter
public class DurationConverter implements AttributeConverter<Duration, PGInterval> {

    @Override
    public PGInterval convertToDatabaseColumn(Duration duration) {
        try {
            return new PGInterval(duration.toString());
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public Duration convertToEntityAttribute(PGInterval pgInterval) {
        if (pgInterval.getYears() == 0 && pgInterval.getMonths() == 0) {
            return Duration.ofDays(pgInterval.getDays())
                    .plusHours(pgInterval.getHours())
                    .plusMinutes(pgInterval.getMinutes())
                    .plusSeconds(pgInterval.getWholeSeconds())
                    .plusMillis(pgInterval.getMicroSeconds() / 1000);
        }
        else {
            throw new RuntimeException(String.format("Cannot convert interval with %s years and %s months to Duration that needs a precise interval", pgInterval.getYears(), pgInterval.getMonths()));
        }
    }
}

I even tried to create a Hibernate UserType:

@Component
@TypeDef(defaultForType = Duration.class, typeClass = DurationType.class)
public class DurationType implements UserType {

  public int[] sqlTypes() {
    return new int[] {Types.OTHER};
  }

  public Class<?> returnedClass() {
    return Duration.class;
  }

  @Override
  public boolean equals(Object o1, Object o2) throws HibernateException {
    return o1.equals(o2);
  }

  @Override
  public int hashCode(Object o) throws HibernateException {
    return o.hashCode();
  }

  @Override
  public Object nullSafeGet(
      ResultSet resultSet,
      String[] names,
      SharedSessionContractImplementor sharedSessionContractImplementor,
      Object o)
      throws HibernateException, SQLException {
    try {
      final PGInterval pgi = (PGInterval) resultSet.getObject(names[0]);

      final int years = pgi.getYears();
      final int months = pgi.getMonths();
      final int days = pgi.getDays();
      final int hours = pgi.getHours();
      final int mins = pgi.getMinutes();
      final int seconds = pgi.getWholeSeconds();
      final int microseconds = pgi.getMicroSeconds();

      if (years == 0 && months == 0) {
        return Duration.ofDays(days)
            .plusHours(hours)
            .plusMinutes(mins)
            .plusSeconds(seconds)
            .plusMillis(microseconds / 1000);
      } else {
        return null;
      }

    } catch (Exception e) {
      return null;
    }
  }

  @Override
  public void nullSafeSet(
      PreparedStatement statement,
      Object value,
      int index,
      SharedSessionContractImplementor sharedSessionContractImplementor)
      throws HibernateException, SQLException {
    if (value == null) {
      statement.setNull(index, Types.OTHER);
    } else {

      final Duration duration = ((Duration) value);

      final int days = (int) duration.toDaysPart();
      final int hours = duration.toHoursPart();
      final int mins = duration.toMinutesPart();
      final int secs = duration.toSecondsPart();

      final PGInterval pgi = new PGInterval(0, 0, days, hours, mins, secs);
      statement.setObject(index, pgi);
    }
  }


  public boolean isMutable() {
    return false;
  }

  public Serializable disassemble(Object value) throws HibernateException {
    throw new HibernateException("not implemented");
  }

  public Object assemble(Serializable cached, Object owner) throws HibernateException {
    throw new HibernateException("not implemented");
  }

  public Object replace(Object original, Object target, Object owner) throws HibernateException {
    throw new HibernateException("not implemented");
  }
}

But in this case, Duration gets translated to bigint.

Is there a way to get Spring Data JPA to send an interval when translating query method parameters?