Timestampdiff function in criteria query

Hibernate ORM
1

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?

2

It’s quite simple, use the Hibernate ORM APIs :wink:

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

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?

4

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.