{"record":{"id":"1e73b0d9b33f6503","repo":"hibernate/hibernate-orm","slug":"unrecognized-field-unit-1e73b0","errorCode":null,"errorMessage":"unrecognized field: {unit}","messagePattern":"unrecognized field: (.+?)","errorType":"exception","errorClass":"SemanticException","httpStatus":null,"severity":"error","filePath":"hibernate-community-dialects/src/main/java/org/hibernate/community/dialect/SQLiteDialect.java","lineNumber":243,"sourceCode":"\t\t\t\textractField( pattern, MONTH, unit );\n\t\t\t\tpattern.append( \")\" );\n\t\t\t\tbreak;\n\t\t\tcase WEEK: //week is not supported by extract() when the argument is a duration\n\t\t\tcase DAY:\n\t\t\t\textractField( pattern, DAY, unit );\n\t\t\t\tbreak;\n\t\t\t//in order to avoid multiple calls to extract(),\n\t\t\t//we use extract(epoch from x - y) * factor for\n\t\t\t//all the following units:\n\t\t\tcase HOUR:\n\t\t\tcase MINUTE:\n\t\t\tcase SECOND:\n\t\t\tcase NANOSECOND:\n\t\t\tcase NATIVE:\n\t\t\t\textractField( pattern, EPOCH, unit );\n\t\t\t\tbreak;\n\t\t\tdefault:\n\t\t\t\tthrow new SemanticException( \"unrecognized field: \" + unit );\n\t\t}\n\t\treturn pattern.toString();\n\t}\n\n\tprivate void extractField(\n\t\t\tStringBuilder pattern,\n\t\t\tTemporalUnit unit,\n\t\t\tTemporalUnit toUnit) {\n\t\tfinal String rhs = extractPattern( unit );\n\t\tfinal String lhs = rhs.replace( \"?2\", \"?3\" );\n\t\tpattern.append( '(');\n\t\tpattern.append( lhs );\n\t\tpattern.append( '-' );\n\t\tpattern.append( rhs );\n\t\tpattern.append(\")\").append( unit.conversionFactor( toUnit, this ) );\n\t}\n\n\t@Override","sourceCodeStart":225,"sourceCodeEnd":261,"githubUrl":"https://github.com/hibernate/hibernate-orm/blob/fad1729dce015f908198d57a8d80274a30f905a5/hibernate-community-dialects/src/main/java/org/hibernate/community/dialect/SQLiteDialect.java#L225-L261","documentation":"Thrown by SQLiteDialect.timestampdiffPattern when Hibernate translates a timestamp difference (HQL timestampdiff()/diff() or duration extraction like '(x - y) by unit') for a temporal unit the SQLite emulation does not cover. The switch only maps YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND, NANOSECOND and NATIVE; every other unit (DECADE, CENTURY, MILLENNIUM, EPOCH, DAY_OF_WEEK, DAY_OF_MONTH, DAY_OF_YEAR) falls to the default branch and aborts query translation with a SemanticException before SQL reaches the database.","triggerScenarios":"Executing HQL such as 'select timestampdiff(dayOfWeek, e.start, e.end) from Event e' or criteria diff(TemporalUnit.DECADE, x, y) against a SQLite database; extracting such a unit from a duration ('(e.end - e.start) by century') which routes through the same pattern.","commonSituations":"Porting queries written for PostgreSQL/Oracle where extract(epoch from x - y) or large calendar units are common; JPA Criteria code that parameterizes the TemporalUnit at runtime; unit tests on SQLite for an application that also runs on other databases.","solutions":["Use a supported diff unit (YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND, NANOSECOND, NATIVE) and convert the result in Java (e.g. divide a YEAR diff by 100 for centuries)","For epoch semantics use extract(epoch from x - y), which the dialect maps to strftime('%s', ...)","Compute calendar-field differences with two extract() calls in HQL instead of timestampdiff()","If unavoidable, subclass SQLiteDialect and override timestampdiffPattern() to render the missing unit"],"exampleFix":"// before - SemanticException: unrecognized field\nselect timestampdiff(dayOfWeek, e.start, e.end) from Event e\n\n// after - diff whole days, derive weekday in Java\nselect timestampdiff(day, e.start, e.end) from Event e","handlingStrategy":"validation","validationCode":"private static final Set<TemporalUnit> SQLITE_DIFF_UNITS = Set.of(\n    TemporalUnit.YEAR, TemporalUnit.QUARTER, TemporalUnit.MONTH, TemporalUnit.WEEK,\n    TemporalUnit.DAY, TemporalUnit.HOUR, TemporalUnit.MINUTE, TemporalUnit.SECOND,\n    TemporalUnit.NANOSECOND, TemporalUnit.NATIVE);\n\nvoid requireSupportedDiffUnit(Dialect dialect, TemporalUnit unit) {\n    if (dialect instanceof SQLiteDialect && !SQLITE_DIFF_UNITS.contains(unit)) {\n        throw new IllegalArgumentException(\n            'SQLite cannot diff by ' + unit + '; use a supported unit and convert in Java');\n    }\n}","typeGuard":"static boolean sqliteSupportsDiffUnit(Dialect dialect, TemporalUnit unit) {\n    return !(dialect instanceof SQLiteDialect) || SQLITE_DIFF_UNITS.contains(unit);\n}","tryCatchPattern":null,"preventionTips":["Whitelist TemporalUnit values per dialect before building dynamic queries","Keep calendar-unit conversion logic in application code for SQLite deployments","Add a SQLite integration test for every HQL function that takes a temporal unit"],"tags":["hibernate","sqlite","dialect","hql","timestampdiff","temporal-unit"],"backgroundTag":"dialect-unsupported-timestampdiff-unit","analyzedSha":"fad1729dce015f908198d57a8d80274a30f905a5","analyzedAt":"2026-08-22T04:13:57.527Z","schemaVersion":2},"datasetVersion":"2026-08-22T09:17:25.309Z"}