With hibernate 7, how to map a java duration to a postgres interval in a typed query?

Question

I'm migrating from hibernate 5 to 7. I use the lib io.hypersistence::hypersistence-utils-hibernate-70:3.10.1 (previously com.vladmihalcea::hibernate-types-52:2.19.1 )

I mapped java.io.duration to postgres interval in my class using the following annotation:

hibernate 5:

import com.vladmihalcea.hibernate.type.interval.PostgreSQLIntervalType;
import org.hibernate.annotations.TypeDef;
@Entity
@TypeDef(
        typeClass = PostgreSQLIntervalType.class,
        defaultForType = Duration.class
)
public class FooConfiguration {

  @Column(columnDefinition = "interval")
  private Duration periodBeforeNextTrigger;

}

Then, I have the following method (written in scala) :

import com.vladmihalcea.hibernate.`type`.interval.PostgreSQLIntervalType
import org.hibernate.jpa.TypedParameterValue

import java.time.Duration
import java.util.Date

def fooInitialTrigger(
    defaultPeriodBeforeInitialTrigger: Duration,
    beforeTs: Date,
  ): F[List[Foo]] =
    T.transactionally { em =>
      F.delay {

        em.createQuery(selectJoinFoo +
         s"""and f.lastTriggerTs is null
             |and f.creationTs + ${caseNull("periodBeforeInitialTrigger", "defaultPeriod")} <= :beforeTs
             |order by f.creationTs desc""".stripMargin,
          classOf[Foo]
        ) .setParameter(
            "defaultPeriod",
            new TypedParameterValue(PostgreSQLIntervalType.INSTANCE, periodBeforeTrigger)
          )
          .setParameter("beforeTs", beforeTs)
          .getResultList
          .asScala
          .toList
      }
    }

private def caseNull(fieldName: String, defaultVarName: String): String =
        s"""(case when conf is null then :$defaultVarName
           |when conf.$fieldName is null then :$defaultVarName
           |else conf.$fieldName
           |end) """.stripMargin

hibernate 7

Migrating the entity is straightforward :

import io.hypersistence.utils.hibernate.type.interval.PostgreSQLIntervalType;
import org.hibernate.annotations.Type;
@Entity
public class FooConfiguration {

  @Column(columnDefinition = "interval")
  @Type(PostgreSQLIntervalType.class)
  private Duration periodBeforeNextTrigger;

}

but I cannot find how to migrate the fooInitialTrigger method. new TypedParameterValue(PostgreSQLIntervalType.INSTANCE, periodBeforeTrigger) does not compile as PostgreSQLIntervalType is not a BindableType

I tried .setParameter("defaultPeriod",periodBeforeTrigger) with (obviously ?) no success.

I tried to implement the following BindableType :

object DurationSqmBindableType extends SqmBindableType[Duration] {
  override def getExpressibleJavaType: JavaType[Duration] =
    new UserTypeJavaTypeWrapper(PostgreSQLIntervalType.INSTANCE)

  override def getSqmType: SqmDomainType[Duration] =
    new CustomType(PostgreSQLIntervalType.INSTANCE, new TypeConfiguration)

  override def getPersistenceType: Type.PersistenceType = PersistenceType.BASIC

  override def getJavaType: Class[Duration] = classOf[Duration]
}

to use in new TypedParameterValue(DurationSqmBindableType, periodBeforeTrigger) but then I have the following error:

org.hibernate.query.sqm.sql.ConversionException: Could not determine ValueMapping for SqmParameter: SqmNamedParameter(defaultPeriod)

Do I miss some configuration ? Am I on the wrong track ?

SANN3 · Accepted Answer · 2025-10-05 05:27:53Z

0

Convert your Duration into a numeric value (like seconds or milliseconds). Example: long periodSeconds = duration.toSeconds();
Pass that numeric parameter to your query instead of a Duration.
Modify your SQL / JPQL (or native query) so that it converts that numeric value back into a PostgreSQL interval using SQL casting.


Query query = entityManager.createNativeQuery("""
    SELECT * 
    FROM foo_configuration f 
    WHERE f.creation_time + (CAST(:defaultPeriodSeconds || ' seconds' AS interval)) <= :now
    """, FooConfiguration.class);

query.setParameter("defaultPeriodSeconds", periodBeforeTrigger.toSeconds());
query.setParameter("now", LocalDateTime.now());

List<FooConfiguration> result = query.getResultList();

answered Oct 5 at 5:27

SANN3

10.2k7 gold badges68 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

With hibernate 7, how to map a java duration to a postgres interval in a typed query?

hibernate 5:

hibernate 7

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

hibernate 5:

hibernate 7

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related