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:

5

Wait, I’ve read all related issues above in JIRA, but I still don’t know how to “date_trunc” in criteria builder. Can anyone provide a example?

I would love to use JPQL or SQL to do this, but our teams prefers criteria builder instead of fixed query string. So I really need to know how to solve this.

@beikov

6

Hibernate ORM 6.2 introduced the org.hibernate.query.criteria.HibernateCriteriaBuilder#truncate(jakarta.persistence.criteria.Expression<T>, org.hibernate.query.sqm.TemporalUnit) method which you can use for this purpose.

HibernateCriteriaBuilder cb = entityManager.unwrap(Session.class).getCriteriaBuilder();
CriteriaQuery<LocalDate> query = cb.createQuery(LocalDate.class);
query.select(
     cb.truncate(cb.localDate(), TemporalUnit.DAY));
LocalDate result = entityManager.createQuery(query).getSingleResult();
7

Thanks! I’m using 6.1.5, is there any way to use this in 6.1.5?

8

This was a new feature introduced with Hibernate 6.2 so you’re going to have to upgrade to make use of it.

9

Hibernate ORM 6.1 is not supported anymore. So if you don’t want to upgrade, you’re out of luck.