hibernate/hibernate-orm · error · SemanticException

unrecognized field: {unit}

Error message

unrecognized field: {unit}

What it means

Thrown by SQLiteDialect.timestampdiffPattern when Hibernate translates a timestamp difference (HQL timestampdiff()/diff() or duration extraction like '(x - y) by unit') for a temporal unit the SQLite emulation does not cover. The switch only maps YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND, NANOSECOND and NATIVE; every other unit (DECADE, CENTURY, MILLENNIUM, EPOCH, DAY_OF_WEEK, DAY_OF_MONTH, DAY_OF_YEAR) falls to the default branch and aborts query translation with a SemanticException before SQL reaches the database.

Source

Thrown at hibernate-community-dialects/src/main/java/org/hibernate/community/dialect/SQLiteDialect.java:243

				extractField( pattern, MONTH, unit );
				pattern.append( ")" );
				break;
			case WEEK: //week is not supported by extract() when the argument is a duration
			case DAY:
				extractField( pattern, DAY, unit );
				break;
			//in order to avoid multiple calls to extract(),
			//we use extract(epoch from x - y) * factor for
			//all the following units:
			case HOUR:
			case MINUTE:
			case SECOND:
			case NANOSECOND:
			case NATIVE:
				extractField( pattern, EPOCH, unit );
				break;
			default:
				throw new SemanticException( "unrecognized field: " + unit );
		}
		return pattern.toString();
	}

	private void extractField(
			StringBuilder pattern,
			TemporalUnit unit,
			TemporalUnit toUnit) {
		final String rhs = extractPattern( unit );
		final String lhs = rhs.replace( "?2", "?3" );
		pattern.append( '(');
		pattern.append( lhs );
		pattern.append( '-' );
		pattern.append( rhs );
		pattern.append(")").append( unit.conversionFactor( toUnit, this ) );
	}

	@Override

View on GitHub (pinned to fad1729dce)

Solutions

1. Use a supported diff unit (YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND, NANOSECOND, NATIVE) and convert the result in Java (e.g. divide a YEAR diff by 100 for centuries)
2. For epoch semantics use extract(epoch from x - y), which the dialect maps to strftime('%s', ...)
3. Compute calendar-field differences with two extract() calls in HQL instead of timestampdiff()
4. If unavoidable, subclass SQLiteDialect and override timestampdiffPattern() to render the missing unit

Example fix

// before - SemanticException: unrecognized field
select timestampdiff(dayOfWeek, e.start, e.end) from Event e

// after - diff whole days, derive weekday in Java
select timestampdiff(day, e.start, e.end) from Event e

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

void requireSupportedDiffUnit(Dialect dialect, TemporalUnit unit) {
    if (dialect instanceof SQLiteDialect && !SQLITE_DIFF_UNITS.contains(unit)) {
        throw new IllegalArgumentException(
            'SQLite cannot diff by ' + unit + '; use a supported unit and convert in Java');
    }
}

static boolean sqliteSupportsDiffUnit(Dialect dialect, TemporalUnit unit) {
    return !(dialect instanceof SQLiteDialect) || SQLITE_DIFF_UNITS.contains(unit);
}

When it happens

Trigger: Executing HQL such as 'select timestampdiff(dayOfWeek, e.start, e.end) from Event e' or criteria diff(TemporalUnit.DECADE, x, y) against a SQLite database; extracting such a unit from a duration ('(e.end - e.start) by century') which routes through the same pattern.

Common situations: Porting queries written for PostgreSQL/Oracle where extract(epoch from x - y) or large calendar units are common; JPA Criteria code that parameterizes the TemporalUnit at runtime; unit tests on SQLite for an application that also runs on other databases.

Related errors

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

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