hibernate/hibernate-orm · error · SemanticException

"Illegal unit for date_add(): " + unit

Error message

"Illegal unit for date_add(): " + unit

What it means

SpannerDialect.timestampaddPattern() throws this SemanticException when Hibernate must render a temporal addition whose operand type is a DATE (not TIMESTAMP/TIME) and the requested TemporalUnit is NANOSECOND, SECOND, MINUTE, HOUR, or NATIVE. Cloud Spanner's date_add() only accepts units of a day or larger, because a DATE column has no time component. The exception surfaces at query translation time, before any SQL reaches the database.

Source

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

				case QUARTER:
				case MONTH:
					throw new SemanticException( "Illegal unit for timestamp_add(): " + unit );
				case WEEK:
					return "timestamp_add(?3, interval cast(?2 * 7 as int64) day)";
				case SECOND:
					return "timestamp_add(?3, interval cast(?2 * 1000000000 as int64) nanosecond)";
				default:
					return "timestamp_add(?3, interval cast(?2 as int64) ?1)";
			}
		}
		else {
			switch ( unit ) {
				case NANOSECOND:
				case SECOND:
				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:

View on GitHub (pinned to fad1729dce)

Solutions

1. Change the entity attribute from LocalDate/DATE to LocalDateTime/TIMESTAMP if sub-day precision is required, then adding seconds/minutes/hours is routed to timestamp_add() which supports them.
2. Rewrite the query to add DAY (or WEEK/MONTH/QUARTER/YEAR) units to the DATE operand instead of sub-day units, since date_add() accepts day-or-larger units.
3. Convert the value to a timestamp first, e.g. `timestampadd(SECOND, n, cast(e.dateField as timestamp))`, so the TIMESTAMP branch of the pattern is used.
4. Move the arithmetic into application code (add a Duration/Period to the LocalDate in Java) instead of doing it in the query.

Example fix

// before (e.dateField is LocalDate -> DATE column)
select timestampadd(SECOND, 30, e.dateField) from Event e

// after (unit of a day or larger works on DATE)
select timestampadd(DAY, 1, e.dateField) from Event e
// or cast to timestamp when sub-day units are needed
select timestampadd(SECOND, 30, cast(e.dateField as timestamp)) from Event e

Set<TemporalUnit> dateOk = EnumSet.of(DAY, WEEK, MONTH, QUARTER, YEAR);
TemporalType operandType = /* from mapping: DATE for LocalDate */
if (operandType == TemporalType.DATE && !dateOk.contains(unit)) {
  throw new IllegalArgumentException("date_add on Spanner DATE supports only " + dateOk + ", got " + unit);
}

try {
  return session.createQuery(hql, Long.class).getSingleResult();
} catch (SemanticException e) {
  if (e.getMessage() != null && e.getMessage().startsWith("Illegal unit for date_add()")) {
    // rewrite query with DAY units or cast to timestamp, then retry
  }
  throw e;
}

When it happens

Trigger: HQL or criteria queries using timestampadd()/dateadd-style arithmetic (e.g. `timestampadd(SECOND, n, e.dateField)` or the `+ seconds` duration operator) where the referenced attribute is mapped to DATE (java.time.LocalDate) on a Cloud Spanner connection. Also triggered by native HQL `timestampadd()` with sub-day units when the inferred operand type is TemporalType.DATE.

Common situations: Porting an application from PostgreSQL/MySQL (where adding seconds to a date silently yields a timestamp) to Cloud Spanner via SpannerDialect; entity attributes annotated @Temporal(TemporalType.DATE) or mapped as LocalDate; tests that pass on other dialects but fail only on the Spanner profile.

Related errors

• "Illegal unit for timestamp_diff(): " + 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/6509ea3210745756. Report an issue: GitHub.