{"record":{"id":"a5a2a9e4c5369598","repo":"hibernate/hibernate-orm","slug":"unrecognized-field-unit-a5a2a9","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/PostgreSQLLegacyDialect.java","lineNumber":553,"sourceCode":"\t\t\t\tcase QUARTER:\n\t\t\t\t\treturn \"(extract(year from ?3-?2)*4+extract(month from ?3-?2)/3)\";\n\t\t\t\tcase MONTH:\n\t\t\t\t\treturn \"(extract(year from ?3-?2)*12+extract(month from ?3-?2))\";\n\t\t\t\tcase WEEK: //week is not supported by extract() when the argument is a duration\n\t\t\t\t\treturn \"(extract(day from ?3-?2)/7)\";\n\t\t\t\tcase DAY:\n\t\t\t\t\treturn \"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\tcase HOUR:\n\t\t\t\tcase MINUTE:\n\t\t\t\tcase SECOND:\n\t\t\t\tcase NANOSECOND:\n\t\t\t\tcase NATIVE:\n\t\t\t\t\treturn \"extract(epoch from ?3-?2)\" + EPOCH.conversionFactor( unit, this );\n\t\t\t\tdefault:\n\t\t\t\t\tthrow new SemanticException( \"unrecognized field: \" + unit );\n\t\t\t}\n\t\t}\n\t}\n\n\t@Deprecated\n\tprotected void extractField(\n\t\t\tStringBuilder pattern,\n\t\t\tTemporalUnit unit,\n\t\t\tTemporalType fromTimestamp,\n\t\t\tTemporalType toTimestamp,\n\t\t\tTemporalUnit toUnit) {\n\t\tpattern.append( \"extract(\" );\n\t\tpattern.append( translateDurationField( unit ) );\n\t\tpattern.append( \" from \" );\n\t\tif ( toTimestamp == TemporalType.DATE && fromTimestamp == TemporalType.DATE ) {\n\t\t\t// special case subtraction of two\n\t\t\t// dates results in an integer not\n\t\t\t// an Interval","sourceCodeStart":535,"sourceCodeEnd":571,"githubUrl":"https://github.com/hibernate/hibernate-orm/blob/fad1729dce015f908198d57a8d80274a30f905a5/hibernate-community-dialects/src/main/java/org/hibernate/community/dialect/PostgreSQLLegacyDialect.java#L535-L571","documentation":"PostgreSQLLegacyDialect.timestampdiffPattern() renders HQL timestampdiff()/date subtraction as 'extract(epoch from ?3-?2)' with a conversion factor. Day, hour, minute, second, nanosecond and NATIVE are handled (year/month/quarter above); any other TemporalUnit (for example WEEK or DAY_OF_WEEK) reaches the default branch and throws SemanticException at query-translation time.","triggerScenarios":"HQL like 'timestampdiff(week, o.startTs, o.endTs)' on PostgreSQLLegacyDialect with a unit outside the supported set.","commonSituations":"Duration reports asking for weeks; query libraries shared across dialects where week arithmetic worked elsewhere.","solutions":["Use day or epoch and scale in the expression ('timestampdiff(day, ...) / 7')","Compute the duration in Java from the two timestamps","Use the non-legacy org.hibernate.dialect.PostgreSQLDialect","Fall back to a native query with EXTRACT/DATE_PART"],"exampleFix":"-- before\nselect timestampdiff(week, o.startTs, o.endTs) from Order o\n\n-- after\nselect timestampdiff(day, o.startTs, o.endTs) / 7 from Order o","handlingStrategy":"validation","validationCode":"private static final Set<TemporalUnit> PG_DIFF_UNITS = EnumSet.of(\n        TemporalUnit.YEAR, TemporalUnit.MONTH, TemporalUnit.QUARTER, TemporalUnit.DAY,\n        TemporalUnit.HOUR, TemporalUnit.MINUTE, TemporalUnit.SECOND,\n        TemporalUnit.NANOSECOND, TemporalUnit.EPOCH, TemporalUnit.NATIVE );\n\nif ( !PG_DIFF_UNITS.contains( unit ) ) {\n    unit = TemporalUnit.DAY; // caller scales (e.g. / 7 for weeks)\n}","typeGuard":"static boolean pgTimestampDiffSupports(TemporalUnit unit) {\n    return EnumSet.of( TemporalUnit.YEAR, TemporalUnit.MONTH, TemporalUnit.QUARTER, TemporalUnit.DAY,\n                       TemporalUnit.HOUR, TemporalUnit.MINUTE, TemporalUnit.SECOND,\n                       TemporalUnit.NANOSECOND, TemporalUnit.EPOCH, TemporalUnit.NATIVE ).contains( unit );\n}","tryCatchPattern":"try {\n    return em.createQuery( hql ).getResultList();\n}\ncatch ( SemanticException e ) {\n    // unsupported diff unit: switch to day/epoch and rescale\n    throw new IllegalArgumentException( \"Use day/epoch and scale\", e );\n}","preventionTips":["Normalize durations to days or epoch seconds in query layers","Convert to weeks/quarters only in presentation code","Test date-math HQL against the PostgreSQL dialect version you ship"],"tags":["postgresql","hql","temporal-unit","query-translation"],"backgroundTag":"unsupported-temporal-unit","analyzedSha":"fad1729dce015f908198d57a8d80274a30f905a5","analyzedAt":"2026-08-22T04:13:57.527Z","schemaVersion":2},"datasetVersion":"2026-08-22T09:17:25.309Z"}