hibernate/hibernate-orm · error · UnsupportedOperationException

Unsupported unit for TIMESTAMPADD:

Error message

Unsupported unit for TIMESTAMPADD: 

What it means

InterSystemsIRISDialect.timestampaddPattern() maps TemporalUnits to IRIS {fn TIMESTAMPADD(...)} / dateadd(...) calls. The switch covers YEAR, QUARTER, MONTH, WEEK, DAY/DAY_OF_MONTH, HOUR, MINUTE, SECOND, NANOSECOND and NATIVE; any other TemporalUnit (for example DAY_OF_WEEK or DAY_OF_YEAR) falls into the default branch and throws UnsupportedOperationException.

Source

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

	@Override
	public String timestampaddPattern(TemporalUnit unit, TemporalType temporalType, IntervalType intervalType) {
		switch (unit) {
			case YEAR:      return "{fn TIMESTAMPADD(SQL_TSI_YEAR, ?2, ?3)}";
			case QUARTER:   return "{fn TIMESTAMPADD(SQL_TSI_QUARTER, ?2, ?3)}";
			case MONTH:     return "{fn TIMESTAMPADD(SQL_TSI_MONTH, ?2, ?3)}";
			case WEEK:      return "{fn TIMESTAMPADD(SQL_TSI_WEEK, ?2, ?3)}";
			case DAY:
			case DAY_OF_MONTH:
				return "{fn TIMESTAMPADD(SQL_TSI_DAY, ?2, ?3)}";
			case HOUR:      return "{fn TIMESTAMPADD(SQL_TSI_HOUR, ?2, ?3)}";
			case MINUTE:    return "{fn TIMESTAMPADD(SQL_TSI_MINUTE, ?2, ?3)}";
			case SECOND:    return "dateadd(second, ?2, ?3)";
			case NANOSECOND:
				return "{fn TIMESTAMPADD(SQL_TSI_FRAC_SECOND, (?2)/1000000, ?3)}";
			case NATIVE:
				return "dateadd(microsecond, ?2, ?3)";
			default:
				throw new UnsupportedOperationException( "Unsupported unit for TIMESTAMPADD: " + unit );
		}
	}

	@SuppressWarnings("deprecation")
	@Override
	public String timestampdiffPattern(TemporalUnit unit,
									TemporalType fromTemporalType,
									TemporalType toTemporalType) {
		if ( unit == null ) {
			return "{fn TIMESTAMPDIFF(SQL_TSI_SECOND, ?2, ?3)}";
		}
		switch (unit) {
			case YEAR:
				return "{fn TIMESTAMPDIFF(SQL_TSI_YEAR, ?2, ?3)}";
			case QUARTER:
				return "({fn TIMESTAMPDIFF(SQL_TSI_MONTH, ?2, ?3)}/3)";
			case MONTH:
				return "{fn TIMESTAMPDIFF(SQL_TSI_MONTH, ?2, ?3)}";

View on GitHub (pinned to fad1729dce)

Solutions

1. Convert the unit to one IRIS supports before the query: DAY_OF_WEEK/DAY_OF_YEAR -> DAY (compute weekday/year-of-year offsets in Java where semantics differ)
2. Perform the date arithmetic in Java (e.g. LocalDateTime.plusDays) and bind the result as a parameter
3. Extend the dialect by subclassing InterSystemsIRISDialect and overriding timestampaddPattern() for the missing units

Example fix

// before
session.createQuery(
    "select timestamp_add(e.occurred, 1, DAY_OF_WEEK) from Event e") // DAY_OF_WEEK unsupported
    .getResultList();

// after - use DAY, or compute in Java
session.createQuery(
    "select timestamp_add(e.occurred, 1, DAY) from Event e").getResultList();
// or: e.getOccurred().plus(1, ChronoUnit.DAYS)

private static final Set<TemporalUnit> IRIS_TIMESTAMPADD_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);

static TemporalUnit irisSafeAddUnit(TemporalUnit unit) {
    if ( !IRIS_TIMESTAMPADD_UNITS.contains(unit) ) {
        throw new IllegalArgumentException("IRIS does not support timestamp_add with " + unit);
    }
    return unit;
}

static boolean isIrisSupportedUnit(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 unit for TIMESTAMPADD") ) {
        // re-issue with a supported unit or do the arithmetic in Java
    }
    throw e;
}

When it happens

Trigger: Calling the HQL/Hibernate datetime function timestamp_add (or building a Duration expression that lowers to TIMESTAMPADD) with a unit outside the supported set on the IRIS dialect, e.g. 'timestamp_add(e.eventDate, 1, DAY_OF_WEEK)' or the equivalent Java Time offset API with TemporalUnit.DAY_OF_WEEK.

Common situations: Passing java.time.temporal.ChronoUnit/TemporalUnit values straight from application enums into datetime arithmetic; porting date-math code from dialects with exhaustive unit coverage; dynamic query builders that let users pick any unit.

Related errors

• Unsupported TemporalUnit for TIMESTAMPDIFF:
• Unrecognized field:
• Tuple size mismatch
• unsupported duration unit: {unit}
• format() function not supported on Mimer SQL

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