How to use Postgres date_trunc via CriteriaApi in Hibernate 6

Hibernate ORM
1

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.

2

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.

3

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.

4

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: