hibernate/hibernate-orm · error · SemanticException

unsupported duration unit: {unit}

Error message

unsupported duration unit: {unit}

What it means

MimerSQLDialect.timestampdiffPattern() builds a 'cast((?3-?2) <unit>(n) as bigint)' expression and only handles NATIVE, NANOSECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER and YEAR. Any other TemporalUnit reaches the default branch and throws SemanticException('unsupported duration unit: ...') during query translation.

Source

Thrown at hibernate-community-dialects/src/main/java/org/hibernate/community/dialect/MimerSQLDialect.java:253

			case MINUTE:
				pattern.append("minute(10)");
				break;
			case HOUR:
				pattern.append("hour(8)");
				break;
			case DAY:
			case WEEK:
				pattern.append("day(7)");
				break;
			case MONTH:
			case QUARTER:
				pattern.append("month(7)");
				break;
			case YEAR:
				pattern.append("year(7)");
				break;
			default:
				throw new SemanticException("unsupported duration unit: " + unit);
		}
		pattern.append(" as bigint)");
		switch (unit) {
			case WEEK:
				pattern.append("/7");
				break;
			case QUARTER:
				pattern.append("/3");
				break;
			case NATIVE:
			case NANOSECOND:
				pattern.append("*1e9");
				break;
		}
		return pattern.toString();
	}

	@Override

View on GitHub (pinned to fad1729dce)

Solutions

1. Use a supported unit (DAY, WEEK, MONTH, QUARTER, YEAR, HOUR, MINUTE, SECOND, NANOSECOND) — DAY_OF_MONTH/DAY_OF_WEEK/DAY_OF_YEAR are only supported by extract(), not timestamp_diff, on Mimer
2. Compute the difference in Java with ChronoUnit.between and filter in memory or bind the result
3. Subclass MimerSQLDialect and override timestampdiffPattern() to map the extra units (e.g. DAY_OF_MONTH to day(7))

Example fix

// before - extract supports DAY_OF_MONTH on Mimer, timestamp_diff does not
"select timestamp_diff(e.endTs, e.startTs, DAY_OF_MONTH) from Session e"

// after
"select timestamp_diff(e.endTs, e.startTs, DAY) from Session e"

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

static TemporalUnit mimerSafeDiffUnit(TemporalUnit unit) {
    // note: DAY_OF_MONTH/DAY_OF_WEEK/DAY_OF_YEAR work with extract() but NOT timestamp_diff on Mimer
    return MIMER_DIFF_UNITS.contains(unit) ? unit : TemporalUnit.DAY;
}

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

try {
    return session.createQuery(hql).getResultList();
} catch (SemanticException e) {
    if ( String.valueOf(e.getMessage()).startsWith("unsupported duration unit") ) {
        // downgrade the unit to DAY or compute in Java
    }
    throw e;
}

When it happens

Trigger: Calling the HQL timestamp_diff/duration function on Mimer SQL with an uncovered unit, most commonly DAY_OF_MONTH, DAY_OF_WEEK or DAY_OF_YEAR — e.g. 'timestamp_diff(e.t2, e.t1, DAY_OF_MONTH)' — while the sibling extractPattern() happily supports DAY_OF_MONTH/DAY_OF_WEEK/DAY_OF_YEAR, which makes the mismatch easy to hit.

Common situations: Reusing a unit enum originally written for extract() in a duration calculation; porting date-diff utilities from H2/PostgreSQL profiles to Mimer; dynamic report builders letting users choose diff units.

Related errors

• Unrecognized field:
• Unsupported TemporalUnit for TIMESTAMPDIFF:
• unrecognized field: {unit}
• Unrecognized field: {unit}
• Invalid duration unit:

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