{"record":{"id":"d25336dc623669c4","repo":"cube-js/cube","slug":"cannot-transform-interval-expression-interval-d25336","errorCode":null,"errorMessage":"Cannot transform interval expression \"${interval}\" to MySQL dialect","messagePattern":"Cannot transform interval expression \"(.+?)\" to MySQL dialect","errorType":"exception","errorClass":null,"httpStatus":null,"severity":"error","filePath":"packages/cubejs-schema-compiler/src/adapter/MysqlQuery.ts","lineNumber":144,"sourceCode":"      return `'${intervalParsed.day} ${intervalParsed.hour}:${intervalParsed.minute}:${intervalParsed.second}' DAY_SECOND`;\n    } else if (intervalParsed.hour && intervalParsed.minute && intKeys === 2) {\n      return `'${intervalParsed.hour}:${intervalParsed.minute}' HOUR_MINUTE`;\n    } else if (intervalParsed.hour && intervalParsed.minute && intervalParsed.second && intKeys === 3) {\n      return `'${intervalParsed.hour}:${intervalParsed.minute}:${intervalParsed.second}' HOUR_SECOND`;\n    } else if (intervalParsed.minute && intervalParsed.second && intKeys === 2) {\n      return `'${intervalParsed.minute}:${intervalParsed.second}' MINUTE_SECOND`;\n    } else if (intervalParsed.hour && intKeys === 1) {\n      return `${intervalParsed.hour} HOUR`;\n    } else if (intervalParsed.minute && intKeys === 1) {\n      return `${intervalParsed.minute} MINUTE`;\n    } else if (intervalParsed.second && intKeys === 1) {\n      return `${intervalParsed.second} SECOND`;\n    } else if (intervalParsed.millisecond && intKeys === 1) {\n      // MySQL doesn't support MILLISECOND, use MICROSECOND instead (1ms = 1000μs)\n      return `${intervalParsed.millisecond * 1000} MICROSECOND`;\n    }\n\n    throw new Error(`Cannot transform interval expression \"${interval}\" to MySQL dialect`);\n  }\n\n  public escapeColumnName(name: string): string {\n    return `\\`${name}\\``;\n  }\n\n  public seriesSql(timeDimension: BaseTimeDimension): string {\n    const values = timeDimension.timeSeries().map(\n      ([from, to]) => `select '${from}' f, '${to}' t`\n    ).join(' UNION ALL ');\n    return `SELECT TIMESTAMP(dates.f) date_from, TIMESTAMP(dates.t) date_to FROM (${values}) AS dates`;\n  }\n\n  public concatStringsSql(strings: string[]): string {\n    return `CONCAT(${strings.join(', ')})`;\n  }\n\n  public unixTimestampSql(): string {","sourceCodeStart":126,"sourceCodeEnd":162,"githubUrl":"https://github.com/cube-js/cube/blob/7d981676b36392fec34088b9afab6bdcad40207c/packages/cubejs-schema-compiler/src/adapter/MysqlQuery.ts#L126-L162","documentation":"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.","triggerScenarios":"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.","commonSituations":"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.","solutions":["Change the interval to a single unit MySQL supports (SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, YEAR, MICROSECOND).","Split the mixed interval into successive dateAdd/dateSubtract calls in the cube SQL or derived expression.","Round the rolling window / granularity to whole units (e.g. '1 month' instead of '1 month 2 days').","If millisecond precision is needed, MySQL's MICROSECOND path already handles a lone millisecond unit — keep only milliseconds, not a mix."],"exampleFix":"// before\ndateAdd: '1 month 3 days'\n// after\ndateAdd: '1 month' // or two chained adds: + INTERVAL 1 MONTH + INTERVAL 3 DAY","handlingStrategy":"validation","validationCode":"const MYSQL_UNITS = /^(SECOND|MINUTE|HOUR|DAY|WEEK|MONTH|YEAR|MICROSECOND)$/i;\nconst units = String(interval).toLowerCase().match(/(year|quarter|month|week|day|hour|minute|second|millisecond)/g) || [];\nif (units.length !== 1 || !(units[0] === 'millisecond' || MYSQL_UNITS.test(units[0].toUpperCase()))) {\n  throw new Error(`MySQL supports only single-unit intervals, got: ${interval}`);\n}","typeGuard":"const isSingleMysqlInterval = (s: string): boolean => /^[0-9]+ (SECOND|MINUTE|HOUR|DAY|WEEK|MONTH|YEAR|MICROSECOND)s?$/i.test(s.trim());","tryCatchPattern":"try { await cubeApi.load(query); } catch (e) { if (/Cannot transform interval expression/.test(e.message)) {\n  throw new Error(`Fix the interval '${e.message.match(/\"(.*)\"/)?.[1]}' for MySQL: use a single supported unit`); } throw e; }","preventionTips":["Keep rolling windows and custom granularities to single units on MySQL","Avoid 'quarter' and mixed intervals in time dimension expressions","Test date arithmetic against the target dialect in CI","Split complex intervals into chained dateAdd/dateSubtract calls"],"tags":["mysql","interval","date-arithmetic","dialect"],"backgroundTag":"unsupported-interval-unit","analyzedSha":"7d981676b36392fec34088b9afab6bdcad40207c","analyzedAt":"2026-09-02T03:45:10.400Z","contentChangedAt":null,"schemaVersion":2},"datasetVersion":"2026-09-08T15:18:49.778Z"}