hibernate/hibernate-orm · error · UnsupportedOperationException

Unsupported TemporalUnit for TIMESTAMPDIFF:

Error message

Unsupported TemporalUnit for TIMESTAMPDIFF: 

What it means

InterSystemsIRISDialect.timestampdiffPattern() translates timestampdiff calls into {fn TIMESTAMPDIFF(SQL_TSI_*, ...)} expressions. It covers YEAR, QUARTER, MONTH, WEEK, DAY/DAY_OF_MONTH, HOUR, MINUTE, SECOND, NANOSECOND, NATIVE (and a null unit meaning SECOND); every other TemporalUnit hits the default branch and throws UnsupportedOperationException('Unsupported TemporalUnit for TIMESTAMPDIFF: ...').

Source

Thrown at hibernate-community-dialects/src/main/java/org/hibernate/community/dialect/InterSystemsIRISDialect.java:681

			case MONTH:
				return "{fn TIMESTAMPDIFF(SQL_TSI_MONTH, ?2, ?3)}";
			case WEEK:
				return "{fn TIMESTAMPDIFF(SQL_TSI_WEEK, ?2, ?3)}";
			case DAY:
			case DAY_OF_MONTH:
				return "{fn TIMESTAMPDIFF(SQL_TSI_DAY, ?2, ?3)}";
			case HOUR:
				return "{fn TIMESTAMPDIFF(SQL_TSI_HOUR, ?2, ?3)}";
			case MINUTE:
				return "{fn TIMESTAMPDIFF(SQL_TSI_MINUTE, ?2, ?3)}";
			case SECOND:
				return "{fn TIMESTAMPDIFF(SQL_TSI_SECOND, ?2, ?3)}";
			case NANOSECOND:
				return "({fn TIMESTAMPDIFF(SQL_TSI_FRAC_SECOND, ?2, ?3)}*1000000)";
			case NATIVE:
				return "({fn TIMESTAMPDIFF(SQL_TSI_FRAC_SECOND, ?2, ?3)}*1000)";
			default:
				throw new UnsupportedOperationException( "Unsupported TemporalUnit for TIMESTAMPDIFF: " + unit );
		}
	}
	@Override
	public long getFractionalSecondPrecisionInNanos() {
		return 1_000L; //default to nanoseconds for now
	}

	@Override
	public boolean supportsTableCheck() {
		return false;
	}

	@Override
	public LockingClauseStrategy getLockingClauseStrategy(QuerySpec querySpec, LockOptions lockOptions) {
		return NON_CLAUSE_STRATEGY;
	}

	@Override

View on GitHub (pinned to fad1729dce)

Solutions

1. Map the requested unit onto a supported one before building the query (DAY_OF_WEEK/DAY_OF_YEAR -> DAY; compute week-day semantics in Java)
2. Compute differences in Java with java.time.temporal.ChronoUnit.between and filter/compare in memory or via a bound parameter
3. Subclass InterSystemsIRISDialect and override timestampdiffPattern() to supply an IRIS expression for the missing units

Example fix

// before
"select timestamp_diff(e.closedAt, e.openedAt, DAY_OF_YEAR) from Ticket e"

// after
"select timestamp_diff(e.closedAt, e.openedAt, DAY) from Ticket e"
// or compute in Java: ChronoUnit.DAYS.between(opened, closed)

private static final Set<TemporalUnit> IRIS_TIMESTAMPDIFF_UNITS = EnumSet.of(
        TemporalUnit.YEAR, TemporalUnit.QUARTER, TemporalUnit.MONTH, TemporalUnit.WEEK,
        TemporalUnit.DAY, TemporalUnit.DAY_OF_MONTH, TemporalUnit.HOUR, TemporalUnit.MINUTE,
        TemporalUnit.SECOND, TemporalUnit.NANOSECOND, TemporalUnit.NATIVE);

if ( !IRIS_TIMESTAMPDIFF_UNITS.contains(unit) ) {
    throw new IllegalArgumentException("IRIS does not support timestamp_diff with " + unit);
}

static boolean isIrisSupportedDiffUnit(TemporalUnit u) {
    return u == TemporalUnit.YEAR || u == TemporalUnit.QUARTER || u == TemporalUnit.MONTH
        || u == TemporalUnit.WEEK || u == TemporalUnit.DAY || u == TemporalUnit.DAY_OF_MONTH
        || u == TemporalUnit.HOUR || u == TemporalUnit.MINUTE || u == TemporalUnit.SECOND
        || u == TemporalUnit.NANOSECOND || u == TemporalUnit.NATIVE;
}

try {
    return session.createQuery(hql).getResultList();
} catch (UnsupportedOperationException e) {
    if ( String.valueOf(e.getMessage()).startsWith("Unsupported TemporalUnit for TIMESTAMPDIFF") ) {
        // compute with ChronoUnit.between in Java and bind the result
    }
    throw e;
}

When it happens

Trigger: Calling the HQL function timestamp_diff (or a duration-between expression) with a unit not in the switch, e.g. 'timestamp_diff(e.end, e.start, DAY_OF_WEEK)' or the equivalent Duration API with TemporalUnit.DAY_OF_YEAR, on the IRIS dialect.

Common situations: Duration/reporting code that derives its unit from user input or a ChronoUnit enum mapping; queries shared across dialects where the core dialects accept more units; upgrading applications that computed weekday differences elsewhere.

Related errors

• Unsupported unit for TIMESTAMPADD:
• Unrecognized field:
• unsupported duration unit: {unit}
• unrecognized field: {unit}
• Unrecognized field: {unit}

AI-assisted analysis of hibernate/hibernate-orm@fad1729dce (2026-08-22). Data as JSON: /api/errors/b1a5caa486a00488. Report an issue: GitHub.