{"record":{"id":"8e8b8b44433a48a6","repo":"hibernate/hibernate-orm","slug":"illegal-unit-for-timestamp-diff-unit","errorCode":null,"errorMessage":"\"Illegal unit for timestamp_diff(): \" + unit","messagePattern":"\"Illegal unit for timestamp_diff\\(\\): \" \\+ unit","errorType":"exception","errorClass":"SemanticException","httpStatus":null,"severity":"error","filePath":"hibernate-core/src/main/java/org/hibernate/dialect/SpannerDialect.java","lineNumber":985,"sourceCode":"\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:\n\t\t\t\t\treturn \"timestamp_diff(?3, ?2, nanosecond)\";\n\t\t\t\tdefault:\n\t\t\t\t\treturn \"timestamp_diff(?3, ?2, ?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 NATIVE:\n\t\t\t\t\treturn \"(date_diff(?3, ?2, day) * 86400000000000)\";\n\t\t\t\tcase SECOND:\n\t\t\t\t\treturn \"(date_diff(?3, ?2, day) * 86400)\";\n\t\t\t\tcase MINUTE:\n\t\t\t\t\treturn \"(date_diff(?3, ?2, day) * 1440)\";\n\t\t\t\tcase HOUR:","sourceCodeStart":967,"sourceCodeEnd":1003,"githubUrl":"https://github.com/hibernate/hibernate-orm/blob/fad1729dce015f908198d57a8d80274a30f905a5/hibernate-core/src/main/java/org/hibernate/dialect/SpannerDialect.java#L967-L1003","documentation":"SpannerDialect.timestampdiffPattern() throws this SemanticException when either operand of a temporal difference is a TIMESTAMP or TIME and the requested unit is YEAR, QUARTER, or MONTH. Spanner's timestamp_diff() works purely in nanoseconds and cannot express variable-length calendar units, so the dialect rejects them instead of producing wrong results. The error is raised while translating the HQL/Criteria query to SQL.","triggerScenarios":"HQL/criteria using timestampdiff() (or the minus operator between temporals) with YEAR, QUARTER, or MONTH where at least one side is a TIMESTAMP/TIME-mapped attribute, e.g. `timestampdiff(MONTH, e.start, e.end)` with e.start/e.end as Instant/LocalDateTime columns, under SpannerDialect.","commonSituations":"Migrating reporting queries that compute 'months between' or 'age in years' from Oracle/PostgreSQL to Cloud Spanner; queries that ran fine on dialects whose timestampdiffPattern handles calendar units for timestamps.","solutions":["Compute the difference in a fixed unit Spanner supports, e.g. `timestampdiff(DAY, e.start, e.end)`, and derive months/years in application code.","Cast both operands to DATE so the non-timestamp branch of the pattern is used, where YEAR/QUARTER/MONTH are supported: `timestampdiff(MONTH, cast(e.start as date), cast(e.end as date))`.","Register a custom SQMFunctionDescriptor for month/year difference that approximates it with date_diff on date-cast operands.","Move the computation out of the query entirely and calculate Period.between() on fetched values in Java."],"exampleFix":"// before (e.start/e.end are timestamp columns)\nselect timestampdiff(MONTH, e.start, e.end) from Event e\n\n// after (cast to date enables the date_diff branch)\nselect timestampdiff(MONTH, cast(e.start as date), cast(e.end as date)) from Event e","handlingStrategy":"validation","validationCode":"if ((fromType == TemporalType.TIMESTAMP || toType == TemporalType.TIMESTAMP)\n    && (unit == TemporalUnit.YEAR || unit == TemporalUnit.QUARTER || unit == TemporalUnit.MONTH)) {\n  throw new IllegalArgumentException(\"Spanner timestamp_diff cannot express \" + unit + \"; cast operands to date or compute in Java\");\n}","typeGuard":null,"tryCatchPattern":"try {\n  return em.createQuery(hql).getSingleResult();\n} catch (SemanticException e) {\n  if (e.getMessage().startsWith(\"Illegal unit for timestamp_diff()\")) { /* fall back to DAY diff */ }\n  throw e;\n}","preventionTips":["For month/year differences on Spanner, cast both operands to date in HQL or compute Period.between() in Java.","Avoid timestampdiff(YEAR/QUARTER/MONTH) with timestamp operands in queries shared across dialects.","Document per-dialect temporal-unit support in the query library."],"tags":["hibernate","cloud-spanner","dialect","hql","timestamp-diff","semanticexception"],"backgroundTag":"unsupported-datetime-unit","analyzedSha":"fad1729dce015f908198d57a8d80274a30f905a5","analyzedAt":"2026-08-22T04:13:57.527Z","schemaVersion":2},"datasetVersion":"2026-08-22T09:17:25.309Z"}