hibernate/hibernate-orm · error · SemanticException

Unrecognized field: {unit}

Error message

Unrecognized field: {unit}

What it means

CockroachDialect.timestampdiffPattern maps HQL timestampdiff/diff between non-DATE temporals to extract() expressions and supports a fixed unit set: YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND, NANOSECOND and NATIVE (via epoch conversion). Any other TemporalUnit (e.g. MICROSECOND, MILLISECOND, DECADE, CENTURY, MILLENNIUM) reaches the default branch and throws SemanticException('Unrecognized field: <unit>') at query translation time, before any SQL runs.

Source

Thrown at hibernate-core/src/main/java/org/hibernate/dialect/CockroachDialect.java:955

		}
		else {
			return switch (unit) {
				case YEAR -> "extract(year from ?3-?2)";
				case QUARTER -> "(extract(year from ?3-?2)*4+extract(month from ?3-?2)//3)";
				case MONTH -> "(extract(year from ?3-?2)*12+extract(month from ?3-?2))";
				case WEEK -> "(extract(day from ?3-?2)/7)"; // week is not supported by extract() when the argument is a duration
				case DAY -> "extract(day from ?3-?2)";
				//in order to avoid multiple calls to extract(),
				//we use extract(epoch from x - y) * factor for
				//all the following units:
				// Note that CockroachDB also has an extract_duration function which returns an int,
				// but we don't use that here because it is deprecated since v20.
				// We need to use round() instead of cast(... as int) because extract epoch returns
				// float8 which can cause loss-of-precision in some cases
				// https://github.com/cockroachdb/cockroach/issues/72523
				case HOUR, MINUTE, SECOND, NANOSECOND, NATIVE ->
						"round(extract(epoch from ?3-?2)" + EPOCH.conversionFactor( unit, this ) + ")::int";
				default -> throw new SemanticException( "Unrecognized field: " + unit );
			};
		}
	}

	@Override
	public String translateDurationField(TemporalUnit unit) {
		return unit==NATIVE
				? "microsecond"
				: super.translateDurationField( unit );
	}

	@Override
	public void appendDatetimeFormat(SqlAppender appender, String format) {
		appender.appendSql( SpannerDialect.datetimeFormat( format ).result() );
	}

	@Override
	public LimitHandler getLimitHandler() {

View on GitHub (pinned to fad1729dce)

Solutions

1. Use a supported unit and convert (SECOND or NANOSECOND covers everything: microseconds = nanos / 1000)
2. Write the computation as a native query fragment: extract(epoch from (b - a)) and scale in SQL or Java
3. Upgrade Hibernate — later 6.x builds extend the CockroachDB unit handling
4. Subclass CockroachDialect and override timestampdiffPattern for your missing unit (delegate to super for the rest)

Example fix

// before (HQL)
select timestampDiff(MICROSECOND, e.start, e.end) from Event e
// -> SemanticException: Unrecognized field: MICROSECOND

// after (HQL)
select timestampDiff(NANOSECOND, e.start, e.end) / 1000 from Event e

Set<TemporalUnit> cockroachDiffUnits = Set.of(YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND, NANOSECOND, NATIVE);
if (!cockroachDiffUnits.contains(unit)) {
    unit = TemporalUnit.NANOSECOND; // translate and scale afterwards
}
Expression<Long> diff = qb.diff(unit, x, y);

try {
    return em.createQuery(hql, Long.class).getSingleResult();
} catch (org.hibernate.query.SemanticException e) {
    if (e.getMessage().startsWith("Unrecognized field")) {
        return em.createQuery(hqlWithNanosAndScale, Long.class).getSingleResult(); // retry with supported unit
    }
    throw e;
}

When it happens

Trigger: HQL like 'select timestampDiff(MICROSECOND, a.createdAt, b.createdAt)' or the equivalent criteria/HQLParser diff() with a unit outside the supported set; ported queries from the PostgreSQL dialect using exotic diff units; units selected dynamically from configuration.

Common situations: Migrating apps to CockroachDB while keeping micro/millisecond diffs; upgrading Hibernate where newer units exist on other dialects but not Cockroach; criteria builders exposing TemporalUnit as an API parameter; unit tests asserting translated SQL for every unit.

Related errors

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

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