# How to use Postgres date\_trunc via CriteriaApi in Hibernate 6

**URL:** <https://discourse.hibernate.org/t/how-to-use-postgres-date-trunc-via-criteriaapi-in-hibernate-6/7220>\
**Category:** Hibernate ORM\
**Created:** [February 2, 2023, 7:54am UTC](https://discourse.hibernate.org/t/how-to-use-postgres-date-trunc-via-criteriaapi-in-hibernate-6/7220 "2023-02-02T07:54:27Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![reda-alaoui](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/reda-alaoui/32/2243_2.png) [@reda-alaoui](https://discourse.hibernate.org/u/reda-alaoui)\
**Post date:** [February 2, 2023, 7:54am UTC](https://discourse.hibernate.org/t/how-to-use-postgres-date-trunc-via-criteriaapi-in-hibernate-6/7220/1 "2023-02-02T07:54:27Z")

</div>

The following code was working on Hibernate 5.6.x:

```java
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 :

```auto
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](https://hibernate.atlassian.net/browse/HHH-15855) that is similar but its fix has only tests based on HQL.

---

<div class="post-metadata">

**Author:** ![beikov](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/beikov/32/258_2.png) [@beikov](https://discourse.hibernate.org/u/beikov)\
**Post date:** [February 2, 2023, 2:07pm UTC](https://discourse.hibernate.org/t/how-to-use-postgres-date-trunc-via-criteriaapi-in-hibernate-6/7220/2 "2023-02-02T14:07:11Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![reda-alaoui](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/reda-alaoui/32/2243_2.png) [@reda-alaoui](https://discourse.hibernate.org/u/reda-alaoui)\
**Post date:** [February 2, 2023, 2:11pm UTC](https://discourse.hibernate.org/t/how-to-use-postgres-date-trunc-via-criteriaapi-in-hibernate-6/7220/3 "2023-02-02T14:11:21Z")

</div>

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](https://hibernate.atlassian.net/browse/HHH-16130) to be able to use it via the JPA api.

---

<div class="post-metadata">

**Author:** ![beikov](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/beikov/32/258_2.png) [@beikov](https://discourse.hibernate.org/u/beikov)\
**Post date:** [February 2, 2023, 2:29pm UTC](https://discourse.hibernate.org/t/how-to-use-postgres-date-trunc-via-criteriaapi-in-hibernate-6/7220/4 "2023-02-02T14:29:37Z")

</div>

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 😉

---

<div class="post-metadata">

**Author:** ![zisisnotzis](https://avatars.discourse-cdn.com/v4/letter/z/958977/32.png) [@zisisnotzis](https://discourse.hibernate.org/u/zisisnotzis)\
**Post date:** [November 21, 2023, 9:09am UTC](https://discourse.hibernate.org/t/how-to-use-postgres-date-trunc-via-criteriaapi-in-hibernate-6/7220/5 "2023-11-21T09:09:57Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![beikov](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/beikov/32/258_2.png) [@beikov](https://discourse.hibernate.org/u/beikov)\
**Post date:** [November 21, 2023, 10:39am UTC](https://discourse.hibernate.org/t/how-to-use-postgres-date-trunc-via-criteriaapi-in-hibernate-6/7220/6 "2023-11-21T10:39:47Z")

</div>

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.

```java
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();

```

---

<div class="post-metadata">

**Author:** ![zisisnotzis](https://avatars.discourse-cdn.com/v4/letter/z/958977/32.png) [@zisisnotzis](https://discourse.hibernate.org/u/zisisnotzis)\
**Post date:** [November 22, 2023, 7:15am UTC](https://discourse.hibernate.org/t/how-to-use-postgres-date-trunc-via-criteriaapi-in-hibernate-6/7220/7 "2023-11-22T07:15:41Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![mbladel](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/mbladel/32/2128_2.png) [@mbladel](https://discourse.hibernate.org/u/mbladel)\
**Post date:** [November 22, 2023, 4:54pm UTC](https://discourse.hibernate.org/t/how-to-use-postgres-date-trunc-via-criteriaapi-in-hibernate-6/7220/8 "2023-11-22T16:54:26Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![beikov](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/beikov/32/258_2.png) [@beikov](https://discourse.hibernate.org/u/beikov)\
**Post date:** [November 23, 2023, 9:48am UTC](https://discourse.hibernate.org/t/how-to-use-postgres-date-trunc-via-criteriaapi-in-hibernate-6/7220/9 "2023-11-23T09:48:01Z")

</div>

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