# Timestampdiff function in criteria query

**URL:** <https://discourse.hibernate.org/t/timestampdiff-function-in-criteria-query/11033>\
**Category:** Hibernate ORM\
**Created:** [February 11, 2025, 8:19am UTC](https://discourse.hibernate.org/t/timestampdiff-function-in-criteria-query/11033 "2025-02-11T08:19:28Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![theigl](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/theigl/32/3835_2.png) [@theigl](https://discourse.hibernate.org/u/theigl)\
**Post date:** [February 11, 2025, 8:19am UTC](https://discourse.hibernate.org/t/timestampdiff-function-in-criteria-query/11033/1 "2025-02-11T08:19:28Z")

</div>

Hi,

I’m trying to get `timestampdiff` to work in a criteria query but I’m unable to pass the unit parameter in a way that Hibernate 6 will accept.

This is my function:

```
public Expression<Number> timestampDiff(Path<Date> date) {
	return cb.function("TIMESTAMPDIFF", Number.class, cb.literal("YEAR"), date, cb.currentTimestamp());
}

```

If I use a literal for the unit like above, the following exception is thrown:

> Caused by: org.hibernate.query.sqm.produce.function.FunctionArgumentException: Parameter 1 of function ‘timestampdiff()’ has type ‘TEMPORAL\_UNIT’, but argument is of type ‘java.lang.Object’

I also tried with a literal of type `TemporalUnit.YEAR` but that results in

> java.lang.ClassCastException: class org.hibernate.query.sqm.tree.expression.SqmLiteral cannot be cast to class org.hibernate.query.sqm.tree.expression.SqmDurationUnit

Any ideas how I can invoke this function correctly?

---

<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 11, 2025, 9:48am UTC](https://discourse.hibernate.org/t/timestampdiff-function-in-criteria-query/11033/2 "2025-02-11T09:48:45Z")

</div>

It’s quite simple, use the Hibernate ORM APIs 😉

```java
HibernateCriteriaBuilder hcb = (HibernateCriteriaBuilder) cb;
hcb.durationByUnit(TemporalUnit.YEAR, hcb.durationBetween(hcb.currentInstant(), (Expression) date));

```

---

<div class="post-metadata">

**Author:** ![theigl](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/theigl/32/3835_2.png) [@theigl](https://discourse.hibernate.org/u/theigl)\
**Post date:** [February 11, 2025, 11:35am UTC](https://discourse.hibernate.org/t/timestampdiff-function-in-criteria-query/11033/3 "2025-02-11T11:35:47Z")

</div>

Ok thanks! I wanted to avoid using Hibernate-specific extensions, but if this is the only way then I’ll use it.

Just to make sure: Is there no way to express this using a custom function?

---

<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 11, 2025, 12:41pm UTC](https://discourse.hibernate.org/t/timestampdiff-function-in-criteria-query/11033/4 "2025-02-11T12:41:47Z")

</div>

I don’t know why you would want to use a Hibernate ORM specific feature yet avoid using Hibernate ORM APIs. I don’t think any other JPA provider supports calculating the timestamp difference by a particular unit.