What causes "unrecognized field: {unit}" in hibernate/hibernate-orm?

Use day or epoch and scale in the expression ('timestampdiff(day, ...) / 7')

hibernate/hibernate-orm · error · SemanticException

unrecognized field: {unit}

Error message

unrecognized field: {unit}

What it means

PostgreSQLLegacyDialect.timestampdiffPattern() renders HQL timestampdiff()/date subtraction as 'extract(epoch from ?3-?2)' with a conversion factor. Day, hour, minute, second, nanosecond and NATIVE are handled (year/month/quarter above); any other TemporalUnit (for example WEEK or DAY_OF_WEEK) reaches the default branch and throws SemanticException at query-translation time.

Source

Thrown at hibernate-community-dialects/src/main/java/org/hibernate/community/dialect/PostgreSQLLegacyDialect.java:553

				case QUARTER:
					return "(extract(year from ?3-?2)*4+extract(month from ?3-?2)/3)";
				case MONTH:
					return "(extract(year from ?3-?2)*12+extract(month from ?3-?2))";
				case WEEK: //week is not supported by extract() when the argument is a duration
					return "(extract(day from ?3-?2)/7)";
				case DAY:
					return "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:
				case HOUR:
				case MINUTE:
				case SECOND:
				case NANOSECOND:
				case NATIVE:
					return "extract(epoch from ?3-?2)" + EPOCH.conversionFactor( unit, this );
				default:
					throw new SemanticException( "unrecognized field: " + unit );
			}
		}
	}

	@Deprecated
	protected void extractField(
			StringBuilder pattern,
			TemporalUnit unit,
			TemporalType fromTimestamp,
			TemporalType toTimestamp,
			TemporalUnit toUnit) {
		pattern.append( "extract(" );
		pattern.append( translateDurationField( unit ) );
		pattern.append( " from " );
		if ( toTimestamp == TemporalType.DATE && fromTimestamp == TemporalType.DATE ) {
			// special case subtraction of two
			// dates results in an integer not
			// an Interval

View on GitHub (pinned to fad1729dce)

Solutions

Use day or epoch and scale in the expression ('timestampdiff(day, ...) / 7')
Compute the duration in Java from the two timestamps
Use the non-legacy org.hibernate.dialect.PostgreSQLDialect
Fall back to a native query with EXTRACT/DATE_PART

Example fix

-- before
select timestampdiff(week, o.startTs, o.endTs) from Order o

-- after
select timestampdiff(day, o.startTs, o.endTs) / 7 from Order o

Defensive patterns

Strategy: validation

Validate before calling

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

if ( !PG_DIFF_UNITS.contains( unit ) ) {
    unit = TemporalUnit.DAY; // caller scales (e.g. / 7 for weeks)
}

Type guard

static boolean pgTimestampDiffSupports(TemporalUnit unit) {
    return EnumSet.of( TemporalUnit.YEAR, TemporalUnit.MONTH, TemporalUnit.QUARTER, TemporalUnit.DAY,
                       TemporalUnit.HOUR, TemporalUnit.MINUTE, TemporalUnit.SECOND,
                       TemporalUnit.NANOSECOND, TemporalUnit.EPOCH, TemporalUnit.NATIVE ).contains( unit );
}

Try / catch

try {
    return em.createQuery( hql ).getResultList();
}
catch ( SemanticException e ) {
    // unsupported diff unit: switch to day/epoch and rescale
    throw new IllegalArgumentException( "Use day/epoch and scale", e );
}

Prevention

Normalize durations to days or epoch seconds in query layers
Convert to weeks/quarters only in presentation code
Test date-math HQL against the PostgreSQL dialect version you ship

When it happens

Trigger: HQL like 'timestampdiff(week, o.startTs, o.endTs)' on PostgreSQLLegacyDialect with a unit outside the supported set.

Common situations: Duration reports asking for weeks; query libraries shared across dialects where week arithmetic worked elsewhere.

Related errors

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