{"record":{"id":"6509ea3210745756","repo":"hibernate/hibernate-orm","slug":"illegal-unit-for-date-add-unit","errorCode":null,"errorMessage":"\"Illegal unit for date_add(): \" + unit","messagePattern":"\"Illegal unit for date_add\\(\\): \" \\+ unit","errorType":"exception","errorClass":"SemanticException","httpStatus":null,"severity":"error","filePath":"hibernate-core/src/main/java/org/hibernate/dialect/SpannerDialect.java","lineNumber":970,"sourceCode":"\t\t\t\tcase QUARTER:\n\t\t\t\tcase MONTH:\n\t\t\t\t\tthrow new SemanticException( \"Illegal unit for timestamp_add(): \" + unit );\n\t\t\t\tcase WEEK:\n\t\t\t\t\treturn \"timestamp_add(?3, interval cast(?2 * 7 as int64) day)\";\n\t\t\t\tcase SECOND:\n\t\t\t\t\treturn \"timestamp_add(?3, interval cast(?2 * 1000000000 as int64) nanosecond)\";\n\t\t\t\tdefault:\n\t\t\t\t\treturn \"timestamp_add(?3, interval cast(?2 as int64) ?1)\";\n\t\t\t}\n\t\t}\n\t\telse {\n\t\t\tswitch ( unit ) {\n\t\t\t\tcase NANOSECOND:\n\t\t\t\tcase SECOND:\n\t\t\t\tcase MINUTE:\n\t\t\t\tcase HOUR:\n\t\t\t\tcase NATIVE:\n\t\t\t\t\tthrow new SemanticException( \"Illegal unit for date_add(): \" + unit );\n\t\t\t\tdefault:\n\t\t\t\t\treturn \"date_add(?3, interval cast(?2 as int64) ?1)\";\n\t\t\t}\n\t\t}\n\t}\n\n\t@Override\n\tpublic String timestampdiffPattern(TemporalUnit unit, TemporalType fromTemporalType, TemporalType toTemporalType) {\n\t\tif ( toTemporalType == TemporalType.TIMESTAMP || fromTemporalType == TemporalType.TIMESTAMP\n\t\t\t|| toTemporalType == TemporalType.TIME || fromTemporalType == TemporalType.TIME ) {\n\t\t\tswitch ( unit ) {\n\t\t\t\tcase YEAR:\n\t\t\t\tcase QUARTER:\n\t\t\t\tcase MONTH:\n\t\t\t\t\tthrow new SemanticException( \"Illegal unit for timestamp_diff(): \" + unit );\n\t\t\t\tcase WEEK:\n\t\t\t\t\treturn \"div(timestamp_diff(?3, ?2, day), 7)\";\n\t\t\t\tcase NATIVE:","sourceCodeStart":952,"sourceCodeEnd":988,"githubUrl":"https://github.com/hibernate/hibernate-orm/blob/fad1729dce015f908198d57a8d80274a30f905a5/hibernate-core/src/main/java/org/hibernate/dialect/SpannerDialect.java#L952-L988","documentation":"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.","triggerScenarios":"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.","commonSituations":"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.","solutions":["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.","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.","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.","Move the arithmetic into application code (add a Duration/Period to the LocalDate in Java) instead of doing it in the query."],"exampleFix":"// before (e.dateField is LocalDate -> DATE column)\nselect timestampadd(SECOND, 30, e.dateField) from Event e\n\n// after (unit of a day or larger works on DATE)\nselect timestampadd(DAY, 1, e.dateField) from Event e\n// or cast to timestamp when sub-day units are needed\nselect timestampadd(SECOND, 30, cast(e.dateField as timestamp)) from Event e","handlingStrategy":"validation","validationCode":"Set<TemporalUnit> dateOk = EnumSet.of(DAY, WEEK, MONTH, QUARTER, YEAR);\nTemporalType operandType = /* from mapping: DATE for LocalDate */\nif (operandType == TemporalType.DATE && !dateOk.contains(unit)) {\n  throw new IllegalArgumentException(\"date_add on Spanner DATE supports only \" + dateOk + \", got \" + unit);\n}","typeGuard":null,"tryCatchPattern":"try {\n  return session.createQuery(hql, Long.class).getSingleResult();\n} catch (SemanticException e) {\n  if (e.getMessage() != null && e.getMessage().startsWith(\"Illegal unit for date_add()\")) {\n    // rewrite query with DAY units or cast to timestamp, then retry\n  }\n  throw e;\n}","preventionTips":["On Spanner, only add DAY or larger units to DATE-mapped attributes; do sub-day arithmetic on timestamp-typed values.","Run a dialect-matrix integration test that exercises every temporal unit used in HQL.","Keep temporal arithmetic in application code when the target dialect set includes Spanner."],"tags":["hibernate","cloud-spanner","dialect","hql","date-arithmetic","semanticexception"],"backgroundTag":"unsupported-datetime-unit","analyzedSha":"fad1729dce015f908198d57a8d80274a30f905a5","analyzedAt":"2026-08-22T04:13:57.527Z","schemaVersion":2},"datasetVersion":"2026-08-22T09:17:25.309Z"}