cube-js/cube · error

Cannot transform interval expression "${interval}" to MySQL

Error message

Cannot transform interval expression "${interval}" to MySQL dialect

What it means

MysqlQuery.formatInterval() cannot express the parsed interval unit(s) in MySQL INTERVAL syntax. MySQL supports a fixed set of interval units; combinations involving units MySQL lacks (e.g. mixed month+second, quarter, or unsupported multi-unit intervals) fall through all branches and hit this throw.

Source

Thrown at packages/cubejs-schema-compiler/src/adapter/MysqlQuery.ts:144

      return `'${intervalParsed.day} ${intervalParsed.hour}:${intervalParsed.minute}:${intervalParsed.second}' DAY_SECOND`;
    } else if (intervalParsed.hour && intervalParsed.minute && intKeys === 2) {
      return `'${intervalParsed.hour}:${intervalParsed.minute}' HOUR_MINUTE`;
    } else if (intervalParsed.hour && intervalParsed.minute && intervalParsed.second && intKeys === 3) {
      return `'${intervalParsed.hour}:${intervalParsed.minute}:${intervalParsed.second}' HOUR_SECOND`;
    } else if (intervalParsed.minute && intervalParsed.second && intKeys === 2) {
      return `'${intervalParsed.minute}:${intervalParsed.second}' MINUTE_SECOND`;
    } else if (intervalParsed.hour && intKeys === 1) {
      return `${intervalParsed.hour} HOUR`;
    } else if (intervalParsed.minute && intKeys === 1) {
      return `${intervalParsed.minute} MINUTE`;
    } else if (intervalParsed.second && intKeys === 1) {
      return `${intervalParsed.second} SECOND`;
    } else if (intervalParsed.millisecond && intKeys === 1) {
      // MySQL doesn't support MILLISECOND, use MICROSECOND instead (1ms = 1000μs)
      return `${intervalParsed.millisecond * 1000} MICROSECOND`;
    }

    throw new Error(`Cannot transform interval expression "${interval}" to MySQL dialect`);
  }

  public escapeColumnName(name: string): string {
    return `\`${name}\``;
  }

  public seriesSql(timeDimension: BaseTimeDimension): string {
    const values = timeDimension.timeSeries().map(
      ([from, to]) => `select '${from}' f, '${to}' t`
    ).join(' UNION ALL ');
    return `SELECT TIMESTAMP(dates.f) date_from, TIMESTAMP(dates.t) date_to FROM (${values}) AS dates`;
  }

  public concatStringsSql(strings: string[]): string {
    return `CONCAT(${strings.join(', ')})`;
  }

  public unixTimestampSql(): string {

View on GitHub (pinned to 7d981676b3)

Solutions

1. Change the interval to a single unit MySQL supports (SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, YEAR, MICROSECOND).
2. Split the mixed interval into successive dateAdd/dateSubtract calls in the cube SQL or derived expression.
3. Round the rolling window / granularity to whole units (e.g. '1 month' instead of '1 month 2 days').
4. If millisecond precision is needed, MySQL's MICROSECOND path already handles a lone millisecond unit — keep only milliseconds, not a mix.

Example fix

// before
dateAdd: '1 month 3 days'
// after
dateAdd: '1 month' // or two chained adds: + INTERVAL 1 MONTH + INTERVAL 3 DAY

const MYSQL_UNITS = /^(SECOND|MINUTE|HOUR|DAY|WEEK|MONTH|YEAR|MICROSECOND)$/i;
const units = String(interval).toLowerCase().match(/(year|quarter|month|week|day|hour|minute|second|millisecond)/g) || [];
if (units.length !== 1 || !(units[0] === 'millisecond' || MYSQL_UNITS.test(units[0].toUpperCase()))) {
  throw new Error(`MySQL supports only single-unit intervals, got: ${interval}`);
}

const isSingleMysqlInterval = (s: string): boolean => /^[0-9]+ (SECOND|MINUTE|HOUR|DAY|WEEK|MONTH|YEAR|MICROSECOND)s?$/i.test(s.trim());

try { await cubeApi.load(query); } catch (e) { if (/Cannot transform interval expression/.test(e.message)) {
  throw new Error(`Fix the interval '${e.message.match(/"(.*)"/)?.[1]}' for MySQL: use a single supported unit`); } throw e; }

When it happens

Trigger: A date arithmetic expression (addInterval/subtractInterval, dateAdd/dateSubtract in cube SQL templates, custom granularity, rolling windows) produces an interval string whose unit combination is not representable in MySQL, e.g. '1 month 3 days' or unsupported units like quarter or millisecond combined with others.

Common situations: Using rollingWindow with an 'other' interval or custom granularity granularity like '2 months 1 week' against a MySQL backend; migrating queries built for Postgres (which supports mixed intervals) to MySQL.

Related errors

• Cannot transform interval expression "${interval}" to Databr
• Unsupported interval unit "${unit}" for the Pinot dialect
• Cannot transform interval expression "${interval}" to Dremio
• MySQL can not work with table names longer than 64 symbols.
• ${this.constructor} driver supports only rows upload

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