How to use Postgres date_trunc via CriteriaApi in Hibernate 6

The following code was working on Hibernate 5.6.x:

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Date> query = cb.createQuery(Date.class);
query.select(
     cb.function(
         "date_trunc", Date.class, cb.literal("day"), cb.currentTimestamp()));
Date result = entityManager.createQuery(query).getSingleResult();

On Hibernate 6.1.6, it fails with :

org.hibernate.QueryException: Parameter 1 of function date_trunc() has type TEMPORAL_UNIT, but argument is of type java.lang.Object

	at org.hibernate.query.sqm.produce.function.ArgumentTypesValidator.throwError(ArgumentTypesValidator.java:271)
	at org.hibernate.query.sqm.produce.function.ArgumentTypesValidator.validate(ArgumentTypesValidator.java:103)
	at org.hibernate.query.sqm.function.AbstractSqmFunctionDescriptor.generateSqmExpression(AbstractSqmFunctionDescriptor.java:104)
	at org.hibernate.query.sqm.internal.SqmCriteriaNodeBuilder.function(SqmCriteriaNodeBuilder.java:1493)
	at org.hibernate.query.sqm.internal.SqmCriteriaNodeBuilder.function(SqmCriteriaNodeBuilder.java:153)

Parameter 1 is cb.literal(‘day’). I don’t understand how to pass a TEMPORAL_UNIT java type via CriteriaApi. Also found [HHH-15855] - Hibernate JIRA that is similar but its fix has only tests based on HQL.

You can pass
new SqmExtractUnit<>( TemporalUnit.DAY, ((NodeBuilder) cb).getIntegerType(), (NodeBuilder) cb ) instead of cb.literal("day"). We don’t have an API yet for constructing such extract and duration units, but ultimately I envision a method in HibernateCriteriaBuilder to invoke the date_trunc function.

Thank you for your answer.
I don’t depend on Hibernate at compilation time, only JPA. For now, I invoke a Postgres function wrapper as a workaround.

FYI, I created [HHH-16130] - Hibernate JIRA to be able to use it via the JPA api.

Thanks, but just for your own sake, you should maybe consider making use of Hibernate APIs if you want to get the best out of your ORM :wink: