hibernate/hibernate-orm · error · SemanticException

unrecognized field: {unit}

Error message

unrecognized field: {unit}

What it means

OracleLegacyDialect.timestampdiffPattern() renders HQL timestampdiff()/datetime subtraction by decomposing an Oracle interval (year(9) to month, day(9) to second) with extract(). Units outside the handled set for this legacy dialect (for example WEEK, QUARTER, or an unhandled composite) hit the default branch and throw SemanticException during query translation, before SQL execution.

Source

Thrown at hibernate-community-dialects/src/main/java/org/hibernate/community/dialect/OracleLegacyDialect.java:753

					else {
						pattern.append( "(" );
						extractField( pattern, DAY, unit );
						pattern.append( "+" );
						extractField( pattern, HOUR, unit );
						pattern.append( "+" );
						extractField( pattern, MINUTE, unit );
						pattern.append( "+" );
						extractField( pattern, SECOND, unit );
					}
				}
				else {
					pattern.append( "((?3-?2)" );
					pattern.append( TemporalUnit.DAY.conversionFactor( unit, this ) );
				}
				pattern.append( ")" );
				break;
			default:
				throw new SemanticException( "unrecognized field: " + unit );
		}
		return pattern.toString();
	}

	private void extractField(StringBuilder pattern, TemporalUnit unit, TemporalUnit toUnit) {
		pattern.append( "extract(" );
		pattern.append( translateExtractField( unit ) );
		pattern.append( " from (?3-?2)" );
		switch ( unit ) {
			case YEAR:
			case MONTH:
				pattern.append( " year(9) to month" );
				break;
			case DAY:
			case HOUR:
			case MINUTE:
			case SECOND:
				break;

View on GitHub (pinned to fad1729dce)

Solutions

1. Ask for day or second and scale in the select expression (e.g. '/ 7' for weeks)
2. Fetch both timestamps and compute the difference in Java
3. Move to the modern org.hibernate.dialect.OracleDialect if it supports the unit
4. Use a native query with extract()/day-to-second arithmetic

Example fix

-- before
select timestampdiff(week, o.orderDate, o.shipDate) from Order o

-- after
select timestampdiff(day, o.orderDate, o.shipDate) / 7 from Order o

private static final Set<TemporalUnit> ORACLE_TIMESTAMPTDIFF_UNITS = EnumSet.of(
        TemporalUnit.YEAR, TemporalUnit.MONTH, TemporalUnit.DAY,
        TemporalUnit.HOUR, TemporalUnit.MINUTE, TemporalUnit.SECOND );

TemporalUnit effective = ORACLE_TIMESTAMPTDIFF_UNITS.contains( unit )
        ? unit : TemporalUnit.DAY; // then scale the result manually

static boolean oracleTimestampDiffSupports(TemporalUnit unit) {
    return EnumSet.of( TemporalUnit.YEAR, TemporalUnit.MONTH, TemporalUnit.DAY,
                       TemporalUnit.HOUR, TemporalUnit.MINUTE, TemporalUnit.SECOND ).contains( unit );
}

try {
    return session.createQuery( hql, Long.class ).getSingleResult();
}
catch ( SemanticException e ) {
    // unsupported diff unit: switch the HQL to day/second and rescale
    throw new IllegalArgumentException( "Use day/second and scale", e );
}

When it happens

Trigger: HQL like 'timestampdiff(week, o.orderDate, o.shipDate)' or 'o.shipDate - o.orderDate week' on OracleLegacyDialect; Criteria difference expressions built with a unit the switch does not cover.

Common situations: Age/duration reports asking for weeks or quarters; queries copied between dialects with richer unit coverage; dynamic unit selection driven by report filters.

Related errors

• {unit} is not a legal field
• unrecognized field: {unit}
• {unit} is not a legal field
• Insert conflict 'do update' clause with constraint name is n
• Unrecognized field:

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