{"record":{"id":"2fd686526c3d77ec","repo":"hibernate/hibernate-orm","slug":"temporal-unit-not-supported-s","errorCode":null,"errorMessage":"Temporal unit not supported [%s]","messagePattern":"Temporal unit not supported \\[(.+?)\\]","errorType":"exception","errorClass":"UnsupportedOperationException","httpStatus":null,"severity":"error","filePath":"hibernate-core/src/main/java/org/hibernate/dialect/function/OracleTruncFunction.java","lineNumber":180,"sourceCode":"\tprivate static void renderTimestampTruncToSecond(\n\t\t\tSqlAppender sqlAppender,\n\t\t\tSqlAstNode datetime,\n\t\t\tSqlAstTranslator<?> walker) {\n\t\tsqlAppender.appendSql( \"to_date(to_char(\" );\n\t\tdatetime.accept( walker );\n\t\tsqlAppender.appendSql( \",'YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS')\" );\n\t}\n\n\tprivate static String datetimeFormat(TemporalUnit temporalUnit) {\n\t\treturn switch ( temporalUnit ) {\n\t\t\tcase YEAR -> \"YYYY\";\n\t\t\tcase MONTH -> \"MM\";\n\t\t\tcase WEEK -> \"IW\";\n\t\t\tcase DAY -> \"DD\";\n\t\t\tcase HOUR -> \"HH\";\n\t\t\tcase MINUTE -> \"MI\";\n\t\t\tcase SECOND -> null;\n\t\t\tdefault -> throw new UnsupportedOperationException( \"Temporal unit not supported [\" + temporalUnit + \"]\" );\n\t\t};\n\t}\n\n\tprivate static boolean isOffsetOrZonedTimestamp(SqlAstNode datetime) {\n\t\tfinal var castType = getCastType( datetime );\n\t\treturn castType == CastType.OFFSET_TIMESTAMP || castType == CastType.ZONE_TIMESTAMP;\n\t}\n\n\tprivate static boolean isTimestamp(SqlAstNode datetime) {\n\t\treturn getCastType( datetime ) == CastType.TIMESTAMP;\n\t}\n\n\tprivate static String getTimezoneFormat(SqlAstNode datetime) {\n\t\treturn getCastType( datetime ) == CastType.ZONE_TIMESTAMP ? \"TZR\" : \"TZH:TZM\";\n\t}\n\n\tprivate static CastType getCastType(SqlAstNode datetime) {\n\t\tif ( datetime instanceof Expression expression ) {","sourceCodeStart":162,"sourceCodeEnd":198,"githubUrl":"https://github.com/hibernate/hibernate-orm/blob/fad1729dce015f908198d57a8d80274a30f905a5/hibernate-core/src/main/java/org/hibernate/dialect/function/OracleTruncFunction.java#L162-L198","documentation":"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.","triggerScenarios":"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.","commonSituations":"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.","solutions":["Use one of the seven supported units (YEAR, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND)","Emulate quarter with native Oracle SQL, e.g. to_date(to_char(ts, 'YYYY-Q'), 'YYYY-Q'), or derive it from trunc(ts, MONTH)","Use extract(...) plus arithmetic for units Oracle trunc does not model","Upgrade Hibernate — the supported-unit matrix widens across releases"],"exampleFix":"// before\nselect trunc(e.ts, QUARTER) from Event e   -- Oracle dialect: throws\n\n// after\nselect trunc(e.ts, MONTH) from Event e      -- or native: sql(\"to_date(to_char(?1,'YYYY-Q'),'YYYY-Q')\", e.ts)","handlingStrategy":"validation","validationCode":"// Whitelist Oracle-safe trunc units before building the HQL\nstatic final java.util.Set<TemporalUnit> ORACLE_SAFE = java.util.Set.of(\n    TemporalUnit.YEAR, TemporalUnit.MONTH, TemporalUnit.WEEK, TemporalUnit.DAY,\n    TemporalUnit.HOUR, TemporalUnit.MINUTE, TemporalUnit.SECOND);\n\nstatic TemporalUnit oracleTruncUnit(TemporalUnit requested) {\n    if (!ORACLE_SAFE.contains(requested)) {\n        throw new IllegalArgumentException(\"Oracle trunc cannot truncate to \" + requested);\n    }\n    return requested;\n}","typeGuard":null,"tryCatchPattern":"try {\n    return em.createQuery(hql, java.time.LocalDateTime.class).getResultList();\n} catch (UnsupportedOperationException e) {\n    if (e.getMessage() != null && e.getMessage().contains(\"Temporal unit not supported\")) {\n        // fall back to a supported unit or a native sql() fragment\n        return em.createQuery(fallbackHql, java.time.LocalDateTime.class).getResultList();\n    }\n    throw e;\n}","preventionTips":["Keep a per-dialect matrix of supported trunc/date_trunc units in one place","Restrict user-supplied granularity inputs to the dialect-safe whitelist","Smoke-test date-math queries on Oracle in CI, not only on PostgreSQL"],"tags":["hibernate","oracle","trunc","temporal-unit","dialect-limitation"],"backgroundTag":"date-trunc-unit-unsupported","analyzedSha":"fad1729dce015f908198d57a8d80274a30f905a5","analyzedAt":"2026-08-22T04:13:57.527Z","schemaVersion":2},"datasetVersion":"2026-08-22T09:17:25.309Z"}