cube-js/cube · error · UserError

Mixed month/second intervals are not supported for Oracle cu

Error message

Mixed month/second intervals are not supported for Oracle custom granularities: ${interval}

What it means

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.

Source

Thrown at packages/cubejs-schema-compiler/src/adapter/OracleQuery.ts:208

    const originTs = `TO_TIMESTAMP('${origin}', 'YYYY-MM-DD"T"HH24:MI:SS.FF3')`;

    const totalMonths = (parsed.year || 0) * 12 + (parsed.quarter || 0) * 3 + (parsed.month || 0);
    const totalSeconds = (parsed.week || 0) * 604800 + (parsed.day || 0) * 86400 +
      (parsed.hour || 0) * 3600 + (parsed.minute || 0) * 60 + (parsed.second || 0);

    // Pure month-based interval: bin with calendar-accurate month arithmetic.
    if (totalMonths > 0 && totalSeconds === 0) {
      return `ADD_MONTHS(${originTs}, FLOOR(MONTHS_BETWEEN(${source}, ${originTs}) / ${totalMonths}) * ${totalMonths})`;
    }

    // Pure fixed-length interval: bin with second arithmetic.
    // (CAST(... AS DATE) - CAST(... AS DATE)) yields a day count; * 86400 → seconds.
    if (totalSeconds > 0 && totalMonths === 0) {
      const diffSeconds = `(CAST(${source} AS DATE) - CAST(${originTs} AS DATE)) * 86400`;
      return `${originTs} + NUMTODSINTERVAL(FLOOR(${diffSeconds} / ${totalSeconds}) * ${totalSeconds}, 'SECOND')`;
    }

    throw new UserError(`Mixed month/second intervals are not supported for Oracle custom granularities: ${interval}`);
  }

  public seriesSql(timeDimension) {
    const values = timeDimension.timeSeries().map(
      ([from, to]) => `SELECT '${from}' f, '${to}' t FROM DUAL`
    ).join(' UNION ALL ');
    return `SELECT TO_TIMESTAMP(dates.f, 'YYYY-MM-DD"T"HH24:MI:SS.FF3') as ${this.escapeColumnName('date_from')}, ` +
      `TO_TIMESTAMP(dates.t, 'YYYY-MM-DD"T"HH24:MI:SS.FF3') as ${this.escapeColumnName('date_to')} ` +
      `FROM (${values}) dates`;
  }

  public sqlTemplates() {
    const templates = super.sqlTemplates();
    templates.functions.UTCTIMESTAMP = 'SYS_EXTRACT_UTC(SYSTIMESTAMP)';
    // Oracle forbids `AS` before a table/subquery alias.
    templates.expressions.query_aliased = '{{ query }} {{ quoted_alias }}';
    // Oracle `/` on NUMBER keeps the fractional part; TRUNC drops decimal digits
    // (truncation toward zero), matching PostgreSQL integer division

View on GitHub (pinned to 7d981676b3)

Solutions

1. 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').
2. Split mixed intervals into separate granularities or a derived time dimension expression.
3. Use a standard granularity (month/quarter/week) instead of a custom one for calendar units.
4. Bin in seconds only if month precision is not truly required.

Example fix

// before
granularities: [{ name: 'quarter_and_hour', intervals: [2, 'months', 1, 'hour'] }]
// after
granularities: [{ name: 'two_months', intervals: [2, 'month'] }]

const units = String(interval).toLowerCase().match(/(year|quarter|month|week|day|hour|minute|second|millisecond)/g) || [];
const hasMonthFamily = units.some(u => ['year','quarter','month'].includes(u));
const hasSecondFamily = units.some(u => ['day','week','hour','minute','second','millisecond'].includes(u));
if (hasMonthFamily && hasSecondFamily) throw new Error(`Oracle custom granularity cannot mix month and second units: ${interval}`);

const isPureMonthOrPureSecond = (s: string): boolean => {
  const u = s.toLowerCase().match(/(year|quarter|month|week|day|hour|minute|second|millisecond)/g) || [];
  const months = u.filter(x => ['year','quarter','month'].includes(x)).length;
  return u.length > 0 && (months === 0 || months === u.length);
};

try { await cubeApi.load(queryWithCustomGranularity); } catch (e) { if (/Mixed month\/second intervals/.test(e.message)) {
  throw new Error(`Adjust custom granularity interval for Oracle: ${e.message}`); } throw e; }

When it happens

Trigger: 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.

Common situations: 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).

Related errors

• Date bin function, required for custom time dimension granul
• Granularity "${timeDimension.granularity}" not found in time
• Cannot transform interval expression "${interval}" to Databr
• Cannot transform interval expression "${interval}" to Dremio
• Oracle can not work with table names longer than 128 symbols

AI-assisted analysis of cube-js/cube@7d981676b3 (2026-09-02). Data as JSON: /api/errors/597fd993dcbc5d5d. Report an issue: GitHub.