{"record":{"id":"597fd993dcbc5d5d","repo":"cube-js/cube","slug":"mixed-month-second-intervals-are-not-supported-for","errorCode":null,"errorMessage":"Mixed month/second intervals are not supported for Oracle custom granularities: ${interval}","messagePattern":"Mixed month/second intervals are not supported for Oracle custom granularities: (.+?)","errorType":"validation","errorClass":"UserError","httpStatus":null,"severity":"error","filePath":"packages/cubejs-schema-compiler/src/adapter/OracleQuery.ts","lineNumber":208,"sourceCode":"    const originTs = `TO_TIMESTAMP('${origin}', 'YYYY-MM-DD\"T\"HH24:MI:SS.FF3')`;\n\n    const totalMonths = (parsed.year || 0) * 12 + (parsed.quarter || 0) * 3 + (parsed.month || 0);\n    const totalSeconds = (parsed.week || 0) * 604800 + (parsed.day || 0) * 86400 +\n      (parsed.hour || 0) * 3600 + (parsed.minute || 0) * 60 + (parsed.second || 0);\n\n    // Pure month-based interval: bin with calendar-accurate month arithmetic.\n    if (totalMonths > 0 && totalSeconds === 0) {\n      return `ADD_MONTHS(${originTs}, FLOOR(MONTHS_BETWEEN(${source}, ${originTs}) / ${totalMonths}) * ${totalMonths})`;\n    }\n\n    // Pure fixed-length interval: bin with second arithmetic.\n    // (CAST(... AS DATE) - CAST(... AS DATE)) yields a day count; * 86400 → seconds.\n    if (totalSeconds > 0 && totalMonths === 0) {\n      const diffSeconds = `(CAST(${source} AS DATE) - CAST(${originTs} AS DATE)) * 86400`;\n      return `${originTs} + NUMTODSINTERVAL(FLOOR(${diffSeconds} / ${totalSeconds}) * ${totalSeconds}, 'SECOND')`;\n    }\n\n    throw new UserError(`Mixed month/second intervals are not supported for Oracle custom granularities: ${interval}`);\n  }\n\n  public seriesSql(timeDimension) {\n    const values = timeDimension.timeSeries().map(\n      ([from, to]) => `SELECT '${from}' f, '${to}' t FROM DUAL`\n    ).join(' UNION ALL ');\n    return `SELECT TO_TIMESTAMP(dates.f, 'YYYY-MM-DD\"T\"HH24:MI:SS.FF3') as ${this.escapeColumnName('date_from')}, ` +\n      `TO_TIMESTAMP(dates.t, 'YYYY-MM-DD\"T\"HH24:MI:SS.FF3') as ${this.escapeColumnName('date_to')} ` +\n      `FROM (${values}) dates`;\n  }\n\n  public sqlTemplates() {\n    const templates = super.sqlTemplates();\n    templates.functions.UTCTIMESTAMP = 'SYS_EXTRACT_UTC(SYSTIMESTAMP)';\n    // Oracle forbids `AS` before a table/subquery alias.\n    templates.expressions.query_aliased = '{{ query }} {{ quoted_alias }}';\n    // Oracle `/` on NUMBER keeps the fractional part; TRUNC drops decimal digits\n    // (truncation toward zero), matching PostgreSQL integer division","sourceCodeStart":190,"sourceCodeEnd":226,"githubUrl":"https://github.com/cube-js/cube/blob/7d981676b36392fec34088b9afab6bdcad40207c/packages/cubejs-schema-compiler/src/adapter/OracleQuery.ts#L190-L226","documentation":"Oracle dateBin() for custom granularities cannot bin timestamps when the interval mixes calendar (month/quarter/year) and exact-time (seconds and below) units, because Oracle requires different INTERVAL types (NUMTOYMINTERVAL vs NUMTODSINTERVAL) for each. Only pure second-based or pure month-based intervals are supported.","triggerScenarios":"Defining a custom granularity whose interval mixes months and seconds, e.g. '1 month 30 seconds' or '2 months 500 milliseconds', on an Oracle-backed cube, evaluated through dateBin during query compilation.","commonSituations":"Custom granularity definitions copied from Postgres examples where mixed intervals are allowed; quarter intervals combined with day/second offsets; typos like '3 months 1 week' (week counts as days/seconds).","solutions":["Change the custom granularity interval to a single family: pure months (e.g. '1 month', '1 quarter') or pure seconds/milliseconds (e.g. '15 minutes', '500 milliseconds').","Split mixed intervals into separate granularities or a derived time dimension expression.","Use a standard granularity (month/quarter/week) instead of a custom one for calendar units.","Bin in seconds only if month precision is not truly required."],"exampleFix":"// before\ngranularities: [{ name: 'quarter_and_hour', intervals: [2, 'months', 1, 'hour'] }]\n// after\ngranularities: [{ name: 'two_months', intervals: [2, 'month'] }]","handlingStrategy":"validation","validationCode":"const units = String(interval).toLowerCase().match(/(year|quarter|month|week|day|hour|minute|second|millisecond)/g) || [];\nconst hasMonthFamily = units.some(u => ['year','quarter','month'].includes(u));\nconst hasSecondFamily = units.some(u => ['day','week','hour','minute','second','millisecond'].includes(u));\nif (hasMonthFamily && hasSecondFamily) throw new Error(`Oracle custom granularity cannot mix month and second units: ${interval}`);","typeGuard":"const isPureMonthOrPureSecond = (s: string): boolean => {\n  const u = s.toLowerCase().match(/(year|quarter|month|week|day|hour|minute|second|millisecond)/g) || [];\n  const months = u.filter(x => ['year','quarter','month'].includes(x)).length;\n  return u.length > 0 && (months === 0 || months === u.length);\n};","tryCatchPattern":"try { await cubeApi.load(queryWithCustomGranularity); } catch (e) { if (/Mixed month\\/second intervals/.test(e.message)) {\n  throw new Error(`Adjust custom granularity interval for Oracle: ${e.message}`); } throw e; }","preventionTips":["Use either pure calendar units (month/quarter/year) or pure time units, never both","Prefer built-in granularities on Oracle when possible","Unit-test custom granularity SQL generation per dialect","Document dialect restrictions next to custom granularity definitions"],"tags":["oracle","interval","custom-granularity","date-bin"],"backgroundTag":"mixed-interval-unsupported","analyzedSha":"7d981676b36392fec34088b9afab6bdcad40207c","analyzedAt":"2026-09-02T03:45:10.400Z","contentChangedAt":null,"schemaVersion":2},"datasetVersion":"2026-09-08T15:18:49.778Z"}