{"record":{"id":"88d535f672dda3e1","repo":"hibernate/hibernate-orm","slug":"unrecognized-field-unit-88d535","errorCode":null,"errorMessage":"Unrecognized field: {unit}","messagePattern":"Unrecognized field: (.+?)","errorType":"exception","errorClass":"SemanticException","httpStatus":null,"severity":"error","filePath":"hibernate-core/src/main/java/org/hibernate/dialect/CockroachDialect.java","lineNumber":955,"sourceCode":"\t\t}\n\t\telse {\n\t\t\treturn switch (unit) {\n\t\t\t\tcase YEAR -> \"extract(year from ?3-?2)\";\n\t\t\t\tcase QUARTER -> \"(extract(year from ?3-?2)*4+extract(month from ?3-?2)//3)\";\n\t\t\t\tcase MONTH -> \"(extract(year from ?3-?2)*12+extract(month from ?3-?2))\";\n\t\t\t\tcase WEEK -> \"(extract(day from ?3-?2)/7)\"; // week is not supported by extract() when the argument is a duration\n\t\t\t\tcase DAY -> \"extract(day from ?3-?2)\";\n\t\t\t\t//in order to avoid multiple calls to extract(),\n\t\t\t\t//we use extract(epoch from x - y) * factor for\n\t\t\t\t//all the following units:\n\t\t\t\t// Note that CockroachDB also has an extract_duration function which returns an int,\n\t\t\t\t// but we don't use that here because it is deprecated since v20.\n\t\t\t\t// We need to use round() instead of cast(... as int) because extract epoch returns\n\t\t\t\t// float8 which can cause loss-of-precision in some cases\n\t\t\t\t// https://github.com/cockroachdb/cockroach/issues/72523\n\t\t\t\tcase HOUR, MINUTE, SECOND, NANOSECOND, NATIVE ->\n\t\t\t\t\t\t\"round(extract(epoch from ?3-?2)\" + EPOCH.conversionFactor( unit, this ) + \")::int\";\n\t\t\t\tdefault -> throw new SemanticException( \"Unrecognized field: \" + unit );\n\t\t\t};\n\t\t}\n\t}\n\n\t@Override\n\tpublic String translateDurationField(TemporalUnit unit) {\n\t\treturn unit==NATIVE\n\t\t\t\t? \"microsecond\"\n\t\t\t\t: super.translateDurationField( unit );\n\t}\n\n\t@Override\n\tpublic void appendDatetimeFormat(SqlAppender appender, String format) {\n\t\tappender.appendSql( SpannerDialect.datetimeFormat( format ).result() );\n\t}\n\n\t@Override\n\tpublic LimitHandler getLimitHandler() {","sourceCodeStart":937,"sourceCodeEnd":973,"githubUrl":"https://github.com/hibernate/hibernate-orm/blob/fad1729dce015f908198d57a8d80274a30f905a5/hibernate-core/src/main/java/org/hibernate/dialect/CockroachDialect.java#L937-L973","documentation":"CockroachDialect.timestampdiffPattern maps HQL timestampdiff/diff between non-DATE temporals to extract() expressions and supports a fixed unit set: YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND, NANOSECOND and NATIVE (via epoch conversion). Any other TemporalUnit (e.g. MICROSECOND, MILLISECOND, DECADE, CENTURY, MILLENNIUM) reaches the default branch and throws SemanticException('Unrecognized field: <unit>') at query translation time, before any SQL runs.","triggerScenarios":"HQL like 'select timestampDiff(MICROSECOND, a.createdAt, b.createdAt)' or the equivalent criteria/HQLParser diff() with a unit outside the supported set; ported queries from the PostgreSQL dialect using exotic diff units; units selected dynamically from configuration.","commonSituations":"Migrating apps to CockroachDB while keeping micro/millisecond diffs; upgrading Hibernate where newer units exist on other dialects but not Cockroach; criteria builders exposing TemporalUnit as an API parameter; unit tests asserting translated SQL for every unit.","solutions":["Use a supported unit and convert (SECOND or NANOSECOND covers everything: microseconds = nanos / 1000)","Write the computation as a native query fragment: extract(epoch from (b - a)) and scale in SQL or Java","Upgrade Hibernate — later 6.x builds extend the CockroachDB unit handling","Subclass CockroachDialect and override timestampdiffPattern for your missing unit (delegate to super for the rest)"],"exampleFix":"// before (HQL)\nselect timestampDiff(MICROSECOND, e.start, e.end) from Event e\n// -> SemanticException: Unrecognized field: MICROSECOND\n\n// after (HQL)\nselect timestampDiff(NANOSECOND, e.start, e.end) / 1000 from Event e","handlingStrategy":"fallback","validationCode":"Set<TemporalUnit> cockroachDiffUnits = Set.of(YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND, NANOSECOND, NATIVE);\nif (!cockroachDiffUnits.contains(unit)) {\n    unit = TemporalUnit.NANOSECOND; // translate and scale afterwards\n}\nExpression<Long> diff = qb.diff(unit, x, y);","typeGuard":null,"tryCatchPattern":"try {\n    return em.createQuery(hql, Long.class).getSingleResult();\n} catch (org.hibernate.query.SemanticException e) {\n    if (e.getMessage().startsWith(\"Unrecognized field\")) {\n        return em.createQuery(hqlWithNanosAndScale, Long.class).getSingleResult(); // retry with supported unit\n    }\n    throw e;\n}","preventionTips":["Pin timestampdiff units to the supported set when CockroachDB is a target","Wrap unit choice behind one query-helper so unsupported units never reach HQL","Add translation-time unit tests per dialect so unsupported units fail in CI, not production"],"tags":["hibernate","cockroachdb","dialect","hql","temporal-unit","timestampdiff"],"backgroundTag":"unsupported-temporal-unit","analyzedSha":"fad1729dce015f908198d57a8d80274a30f905a5","analyzedAt":"2026-08-22T04:13:57.527Z","schemaVersion":2},"datasetVersion":"2026-08-22T09:17:25.309Z"}