hibernate/hibernate-orm · error · SemanticException

"Illegal unit for timestamp_diff(): " + unit

Error message

"Illegal unit for timestamp_diff(): " + unit

What it means

SpannerDialect.timestampdiffPattern() throws this SemanticException when either operand of a temporal difference is a TIMESTAMP or TIME and the requested unit is YEAR, QUARTER, or MONTH. Spanner's timestamp_diff() works purely in nanoseconds and cannot express variable-length calendar units, so the dialect rejects them instead of producing wrong results. The error is raised while translating the HQL/Criteria query to SQL.

Source

Thrown at hibernate-core/src/main/java/org/hibernate/dialect/SpannerDialect.java:985

				case MINUTE:
				case HOUR:
				case NATIVE:
					throw new SemanticException( "Illegal unit for date_add(): " + unit );
				default:
					return "date_add(?3, interval cast(?2 as int64) ?1)";
			}
		}
	}

	@Override
	public String timestampdiffPattern(TemporalUnit unit, TemporalType fromTemporalType, TemporalType toTemporalType) {
		if ( toTemporalType == TemporalType.TIMESTAMP || fromTemporalType == TemporalType.TIMESTAMP
			|| toTemporalType == TemporalType.TIME || fromTemporalType == TemporalType.TIME ) {
			switch ( unit ) {
				case YEAR:
				case QUARTER:
				case MONTH:
					throw new SemanticException( "Illegal unit for timestamp_diff(): " + unit );
				case WEEK:
					return "div(timestamp_diff(?3, ?2, day), 7)";
				case NATIVE:
					return "timestamp_diff(?3, ?2, nanosecond)";
				default:
					return "timestamp_diff(?3, ?2, ?1)";
			}
		}
		else {
			switch ( unit ) {
				case NANOSECOND:
				case NATIVE:
					return "(date_diff(?3, ?2, day) * 86400000000000)";
				case SECOND:
					return "(date_diff(?3, ?2, day) * 86400)";
				case MINUTE:
					return "(date_diff(?3, ?2, day) * 1440)";
				case HOUR:

View on GitHub (pinned to fad1729dce)

Solutions

1. Compute the difference in a fixed unit Spanner supports, e.g. `timestampdiff(DAY, e.start, e.end)`, and derive months/years in application code.
2. Cast both operands to DATE so the non-timestamp branch of the pattern is used, where YEAR/QUARTER/MONTH are supported: `timestampdiff(MONTH, cast(e.start as date), cast(e.end as date))`.
3. Register a custom SQMFunctionDescriptor for month/year difference that approximates it with date_diff on date-cast operands.
4. Move the computation out of the query entirely and calculate Period.between() on fetched values in Java.

Example fix

// before (e.start/e.end are timestamp columns)
select timestampdiff(MONTH, e.start, e.end) from Event e

// after (cast to date enables the date_diff branch)
select timestampdiff(MONTH, cast(e.start as date), cast(e.end as date)) from Event e

if ((fromType == TemporalType.TIMESTAMP || toType == TemporalType.TIMESTAMP)
    && (unit == TemporalUnit.YEAR || unit == TemporalUnit.QUARTER || unit == TemporalUnit.MONTH)) {
  throw new IllegalArgumentException("Spanner timestamp_diff cannot express " + unit + "; cast operands to date or compute in Java");
}

try {
  return em.createQuery(hql).getSingleResult();
} catch (SemanticException e) {
  if (e.getMessage().startsWith("Illegal unit for timestamp_diff()")) { /* fall back to DAY diff */ }
  throw e;
}

When it happens

Trigger: HQL/criteria using timestampdiff() (or the minus operator between temporals) with YEAR, QUARTER, or MONTH where at least one side is a TIMESTAMP/TIME-mapped attribute, e.g. `timestampdiff(MONTH, e.start, e.end)` with e.start/e.end as Instant/LocalDateTime columns, under SpannerDialect.

Common situations: Migrating reporting queries that compute 'months between' or 'age in years' from Oracle/PostgreSQL to Cloud Spanner; queries that ran fine on dialects whose timestampdiffPattern handles calendar units for timestamps.

Related errors

• "Illegal unit for date_add(): " + unit
• unrecognized field: {unit}
• Unrecognized field: {unit}
• "No create schema syntax supported by " + getClass().getName
• "No drop schema syntax supported by " + getClass().getName()

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