hibernate/hibernate-orm · error · UnsupportedOperationException

Temporal unit not supported [%s]

Error message

Temporal unit not supported [%s]

What it means

Hibernate's Oracle trunc emulation maps datetime fields to Oracle format models: YEAR->YYYY, MONTH->MM, WEEK->IW, DAY->DD, HOUR->HH, MINUTE->MI, and SECOND needs no model. Only those seven units are handled; any other TemporalUnit reaches the default branch of the switch and throws UnsupportedOperationException during SQL rendering.

Source

Thrown at hibernate-core/src/main/java/org/hibernate/dialect/function/OracleTruncFunction.java:180

	private static void renderTimestampTruncToSecond(
			SqlAppender sqlAppender,
			SqlAstNode datetime,
			SqlAstTranslator<?> walker) {
		sqlAppender.appendSql( "to_date(to_char(" );
		datetime.accept( walker );
		sqlAppender.appendSql( ",'YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS')" );
	}

	private static String datetimeFormat(TemporalUnit temporalUnit) {
		return switch ( temporalUnit ) {
			case YEAR -> "YYYY";
			case MONTH -> "MM";
			case WEEK -> "IW";
			case DAY -> "DD";
			case HOUR -> "HH";
			case MINUTE -> "MI";
			case SECOND -> null;
			default -> throw new UnsupportedOperationException( "Temporal unit not supported [" + temporalUnit + "]" );
		};
	}

	private static boolean isOffsetOrZonedTimestamp(SqlAstNode datetime) {
		final var castType = getCastType( datetime );
		return castType == CastType.OFFSET_TIMESTAMP || castType == CastType.ZONE_TIMESTAMP;
	}

	private static boolean isTimestamp(SqlAstNode datetime) {
		return getCastType( datetime ) == CastType.TIMESTAMP;
	}

	private static String getTimezoneFormat(SqlAstNode datetime) {
		return getCastType( datetime ) == CastType.ZONE_TIMESTAMP ? "TZR" : "TZH:TZM";
	}

	private static CastType getCastType(SqlAstNode datetime) {
		if ( datetime instanceof Expression expression ) {

View on GitHub (pinned to fad1729dce)

Solutions

1. Use one of the seven supported units (YEAR, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND)
2. Emulate quarter with native Oracle SQL, e.g. to_date(to_char(ts, 'YYYY-Q'), 'YYYY-Q'), or derive it from trunc(ts, MONTH)
3. Use extract(...) plus arithmetic for units Oracle trunc does not model
4. Upgrade Hibernate — the supported-unit matrix widens across releases

Example fix

// before
select trunc(e.ts, QUARTER) from Event e   -- Oracle dialect: throws

// after
select trunc(e.ts, MONTH) from Event e      -- or native: sql("to_date(to_char(?1,'YYYY-Q'),'YYYY-Q')", e.ts)

// Whitelist Oracle-safe trunc units before building the HQL
static final java.util.Set<TemporalUnit> ORACLE_SAFE = java.util.Set.of(
    TemporalUnit.YEAR, TemporalUnit.MONTH, TemporalUnit.WEEK, TemporalUnit.DAY,
    TemporalUnit.HOUR, TemporalUnit.MINUTE, TemporalUnit.SECOND);

static TemporalUnit oracleTruncUnit(TemporalUnit requested) {
    if (!ORACLE_SAFE.contains(requested)) {
        throw new IllegalArgumentException("Oracle trunc cannot truncate to " + requested);
    }
    return requested;
}

try {
    return em.createQuery(hql, java.time.LocalDateTime.class).getResultList();
} catch (UnsupportedOperationException e) {
    if (e.getMessage() != null && e.getMessage().contains("Temporal unit not supported")) {
        // fall back to a supported unit or a native sql() fragment
        return em.createQuery(fallbackHql, java.time.LocalDateTime.class).getResultList();
    }
    throw e;
}

When it happens

Trigger: HQL: trunc(e.timestamp, QUARTER) or any unit outside YEAR/MONTH/WEEK/DAY/HOUR/MINUTE/SECOND with the Oracle dialect — e.g. units accepted by other dialects' date_trunc but absent from the Oracle switch.

Common situations: Porting PostgreSQL date_trunc('quarter', ...) style queries to Oracle via HQL; shared HQL run across a dialect matrix where Oracle supports the narrowest unit set; version upgrades changing the supported-unit matrix.

Related errors

• Unsupported temporal type: %s
• Unrecognized field:
• Unsupported unit for TIMESTAMPADD:
• Unsupported TemporalUnit for TIMESTAMPDIFF:
• unsupported duration unit: {unit}

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