What causes "Unsupported temporal type: %s" in hibernate/hibernate-orm?

Cast the argument to a plain timestamp: format(cast(e.offsetTs as timestamp), ...)

hibernate/hibernate-orm · error · IllegalArgumentException

Unsupported temporal type: %s

Error message

Unsupported temporal type: %s

What it means

Spanner's format() rendering picks format_date vs format_timestamp from the first argument's temporal type. DATE, TIME and TIMESTAMP are handled; anything else — offset or zoned temporal types (OffsetDateTime, ZonedDateTime, OffsetTime) or a null type — reaches the default branch and throws IllegalArgumentException during SQL rendering.

Source

Thrown at hibernate-core/src/main/java/org/hibernate/dialect/function/SpannerFormatFunction.java:39

 */
public class SpannerFormatFunction extends FormatFunction {
	public SpannerFormatFunction(TypeConfiguration typeConfiguration) {
		super("format_timestamp", true, true, false, typeConfiguration);
	}

	@Override
	public void render(
			SqlAppender sqlAppender,
			List<? extends SqlAstNode> sqlAstArguments,
			ReturnableType<?> returnType,
			SqlAstTranslator<?> walker) {
		var datetime = (Expression) sqlAstArguments.get( 0 );
		var format = sqlAstArguments.get( 1 );
		var temporalType = getSqlTemporalType( datetime.getExpressionType() );
		switch ( temporalType ) {
			case DATE -> sqlAppender.appendSql( "format_date(" );
			case TIME, TIMESTAMP -> sqlAppender.appendSql( "format_timestamp(" );
			default -> throw new IllegalArgumentException( "Unsupported temporal type: " + temporalType );
		}
		format.accept( walker );
		sqlAppender.append( ',' );
		datetime.accept( walker );
		sqlAppender.append( ')' );
	}
}

View on GitHub (pinned to fad1729dce)

Solutions

Cast the argument to a plain timestamp: format(cast(e.offsetTs as timestamp), ...)
Map the attribute as LocalDateTime (or Instant per project convention) for Spanner
Upgrade Hibernate — Spanner temporal type coverage improves across releases

Example fix

// before
select format(e.offsetTs, 'YYYY-MM') from Event e

// after
select format(cast(e.offsetTs as timestamp), 'YYYY-MM') from Event e

Defensive patterns

Strategy: type-guard

Validate before calling

// Spanner format() only accepts DATE/TIME/TIMESTAMP-mapped expressions
static void checkSpannerFormatArg(Class<?> attrType) {
    if (OffsetDateTime.class.equals(attrType) || ZonedDateTime.class.equals(attrType)
            || OffsetTime.class.equals(attrType)) {
        throw new IllegalArgumentException("Cast offset/zoned temporals to timestamp for Spanner: " + attrType);
    }
}

Type guard

static boolean spannerTemporalSafe(Class<?> t) {
    return !(OffsetDateTime.class.equals(t) || ZonedDateTime.class.equals(t) || OffsetTime.class.equals(t));
}

Try / catch

try {
    return em.createQuery(hql, String.class).getResultList();
} catch (IllegalArgumentException e) {
    if (e.getMessage() != null && e.getMessage().contains("Unsupported temporal type")) {
        return em.createQuery(withTimestampCast(hql), String.class).getResultList();
    }
    throw e;
}

Prevention

Map Spanner entities with LocalDate/LocalDateTime/LocalTime, not offset types
Wrap risky expressions in cast(... as timestamp) when porting models
Add an integration test that formats every temporal attribute on Spanner

When it happens

Trigger: HQL: format(e.offsetTs, 'YYYY-MM-DD') on the Cloud Spanner dialect, where the attribute is mapped as OffsetDateTime/ZonedDateTime/OffsetTime.

Common situations: Entities shared between Spanner and other databases; mapping audit timestamps as OffsetDateTime by convention; adopting the Spanner dialect on an existing model.

Related errors

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