Support date arithmetic with `INTERVAL` operators by JanJakes · Pull Request #539 · WordPress/sqlite-database-integration

JanJakes · 2026-10-19T08:35:36Z

Summary

This PR adds date arithmetic with INTERVAL, building on #510 by @samuelmbabhazi:

SELECT d + INTERVAL 1 DAY;
SELECT d - INTERVAL 5 HOUR;
SELECT d + INTERVAL 1 DAY - INTERVAL 2 HOUR;

These queries previously failed with a syntax error. They now use SQLite’s DATE() and DATETIME() functions, sharing the translation with DATE_ADD() and DATE_SUB().

Why the extra fixes

Our current parser groups some expressions differently from MySQL. Translating them directly can produce wrong results—or update or delete the wrong rows.

This PR adds safeguards to the implementation in #510:

Reject expressions the parser cannot group correctly, including INTERVAL 1 DAY + d, 1 + d + INTERVAL 1 DAY, and d + INTERVAL 1 DAY + x. Errors suggest adding parentheses.
Support QUARTER and SQL_TSI_* units, and handle negative and computed interval values.
Reject unsupported units, including MICROSECOND and composite units such as HOUR_MINUTE, instead of silently returning NULL.
Return dates for date-only inputs, as MySQL does. Previously, '2024-01-01' + INTERVAL 1 DAY returned 2024-01-02 00:00:00, so comparisons with DATE values could match the wrong rows. This also fixes DATE_ADD() and DATE_SUB(), the first case of DATE_ADD() and DATE_SUB() overflow past the end of the month, and ADDDATE() and SUBDATE() are not supported #531.

The supported forms cover the linked issues. The expression restrictions can be revisited after the planned switch to a parser based on MySQL’s own grammar.

Remaining limitations

The existing date translation still differs from MySQL in these cases:

Numeric dates: Values such as 20240101 are not interpreted as MySQL dates.
Interval values: MySQL converts the value to a whole number. Here, INTERVAL 1.5 DAY adds 36 hours instead of 2 days, and a string such as ' 1' returns NULL with + but works with -.
Time inputs: TIME values return datetime strings, and times past midnight wrap around instead of going over 24 hours.
Month ends: Month-end arithmetic can overflow into the following month.
ADDDATE() and SUBDATE(): These functions remain unsupported.

Month ends and ADDDATE()/SUBDATE() are tracked in #531.

Expected results in the new tests were checked against MySQL 8.4.

Fixes #454
Fixes #90

Summary by CodeRabbit

New Features
- Improved support for MySQL date and time interval calculations.
- Added support for interval units from seconds through years, including weeks and quarters.
- Supports chained additions and subtractions, computed intervals, and parenthesized expressions.
- Date and datetime results are now handled appropriately across supported calculations.
Bug Fixes
- Improved validation and handling of unsupported or ambiguous interval expressions.
- Preserved correct NULL behavior and date calculations in updates and default values.

coderabbitai · 2026-10-19T08:35:46Z

No actionable comments were generated in the recent review. 🎉

ℹ️ Recent review info

⚙️ Run configuration

Configuration used: defaults
Review profile: CHILL
Plan: Advanced
Run ID: bd9ee2e1-9c7e-48dc-9778-fcc39a87926b

📥 Commits

Reviewing files that changed from the base of the PR and between e88a817 and bc9ea36.

📒 Files selected for processing: 3

packages/mysql-on-sqlite/src/sqlite/class-wp-mysql-on-sqlite.php
packages/mysql-on-sqlite/tests/WP_MySQL_On_SQLite_Tests.php
packages/mysql-on-sqlite/tests/WP_MySQL_On_SQLite_Translation_Tests.php

Included review availability: This review used your included allowance. Your plan provides up to 2 included reviews per hour; 1 remain after this review.

📝 Walkthrough

The MySQL-on-SQLite translator now handles supported interval units in DATE_ADD(), DATE_SUB(), and interval arithmetic expressions. The changes include chained operations, date and datetime result handling, and rejection of unsupported units and parser groupings.

Changes: Interval arithmetic translation

Layer / File(s)	Summary
Shared interval translation `packages/mysql-on-sqlite/src/sqlite/class-wp-mysql-on-sqlite.php`, `packages/mysql-on-sqlite/tests/WP_MySQL_On_SQLite_Tests.php`	The translator maps supported interval units to SQLite modifiers and uses shared interval handling for `DATE_ADD()` and `DATE_SUB()`. Tests cover supported and unsupported units, computed and negative intervals, and updated default-expression output.
Interval arithmetic expressions `packages/mysql-on-sqlite/src/sqlite/class-wp-mysql-on-sqlite.php`, `packages/mysql-on-sqlite/tests/WP_MySQL_On_SQLite_Tests.php`, `packages/mysql-on-sqlite/tests/WP_MySQL_On_SQLite_Translation_Tests.php`	The translator handles chained interval additions and subtractions and rejects unsupported prefix and partial-operand forms. Tests cover date and datetime values, parentheses, comparisons, and database operations.

Priority: ➖ Normal

Estimated code review effort: 3 (Moderate) | ~25 minutes

Change: Feature · Severity of issue fixed: Low

Sequence Diagram(s)

sequenceDiagram
  participant SQLTranslator
  participant translate_bit_expr
  participant translate_interval_arithmetic
  participant SQLite
  SQLTranslator->>translate_bit_expr: translate interval expression
  translate_bit_expr->>translate_interval_arithmetic: pass date and interval operations
  translate_interval_arithmetic->>SQLite: produce DATE or DATETIME modifiers

Suggested reviewers: nandan108

Merge Risk: ⚪ Minimal · up to bc9ea

This change adds MySQL-style INTERVAL date arithmetic with tests checked against MySQL 8.4 and explicit rejection of unsupported forms. A suspected crash with the native parser was checked and is not reachable. No outstanding merge-blocking risk remains; the known gaps are documented and tracked in #531.

Pre-merge checks: 4 · 1

Failed checks: 1 warning

Check name	Status	Explanation	Resolution
Docstring Coverage		Docstring coverage is 25.00% which is insufficient. The required threshold is 80.00%. Docstring coverage is scoped to functions touched by this diff. Analyzed 20 functions across 3 files.	Write docstrings for the functions missing them to satisfy the coverage threshold.

Passed checks: 4 passed

Check name	Status	Explanation
Description Check		Check skipped - CodeRabbit’s high-level summary is enabled.
Title check		The title clearly and concisely describes the main change: adding support for MySQL date arithmetic with INTERVAL operators.
Linked Issues check		The PR meets the coding requirements for [`#454`] and [`#90`]. `translate_bit_expr()` detects interval arithmetic, and `translate_interval_arithmetic()` converts `HOUR` and other supported units to SQLite…
Out of Scope Changes check		The changes remain within the stated interval-arithmetic scope. Unit mappings, parser-grouping safeguards, unsupported-unit errors, date-only result handling, shared `DATE_ADD()`/`DATE_SUB()` translat…

Full details: Linked Issues check

Explanation

The PR meets the coding requirements for [#454] and [#90]. translate_bit_expr() detects interval arithmetic, and translate_interval_arithmetic() converts HOUR and other supported units to SQLite DATE() or DATETIME() modifiers. The shared path also handles DATE_ADD() and DATE_SUB(). Tests cover DATE(... - INTERVAL 5 HOUR), direct interval arithmetic, chained intervals, column expressions, and computed values. These changes address the syntax failure in [#454] and the (NOW() - INTERVAL 1 DAY) form in [#90] through the generic expression translator.

Full details: Out of Scope Changes check

Explanation

The changes remain within the stated interval-arithmetic scope. Unit mappings, parser-grouping safeguards, unsupported-unit errors, date-only result handling, shared DATE_ADD()/DATE_SUB() translation, and their tests support the linked date-arithmetic behavior or prevent incorrect query results. No unrelated product behavior or unrelated files are demonstrated.

Fix all pre-merge checks with AI

✨ Finishing Touches: 💡 1

📝 Generate docstrings: 💡

Commit to this branch
Create a new PR

🧪 Generate unit tests (beta)

Commit to this branch
Create a new PR

Autofix · Keep fixing CodeRabbit findings and required CI, and resolving merge conflicts

Warning

Some tools did not complete. Review the errors below.

🔧 PHPStan (2.2.16)

PHPStan reported 2 non-file analysis error(s); analysis is incomplete

🔧 PHPMD (2.15.0)

packages/mysql-on-sqlite/tests/WP_MySQL_On_SQLite_Tests.php

PHPMD could not process this file (exit code 255): PHP Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 20480 bytes) in phar:///usr/bin/phpmd/vendor/pdepend/pdepend/src/main/php/PDepend/Util/Cache/Driver/FileCacheDriver.php on line 209

Thanks for using CodeRabbit! It's free for OSS, and your support helps us grow. If you like it, consider giving us a shout-out.

❤️ Share

_{Comment @coderabbitai help to get the list of available commands.}

MySQL accepts `expr + INTERVAL n unit`, `expr - INTERVAL n unit` and `INTERVAL n unit + expr`, and the parser already does too, but the translator passed the expression through unchanged, so SQLite rejected it with a syntax error. The three forms now share the DATETIME() translation used by DATE_ADD() and DATE_SUB(), which gains two fixes on the way: a negative value no longer produces a modifier that SQLite rejects, and a computed value is parenthesized so that `||` does not bind to it first. Fixes #454 Fixes #90

The grammar doesn't group "INTERVAL n unit + expr" like MySQL. Its operand includes AND, OR, and XOR, which MySQL applies to the whole expression, so this query deleted every row, while MySQL deletes none: DELETE FROM t WHERE INTERVAL 30 DAY + d < '2025-01-01' AND id = 99 The parser also matches "expr + INTERVAL 1 DAY + x" as "expr + (INTERVAL 1 DAY + x)". The prefix form is rare, and the reported queries use the suffix form, so reject it until the grammar follows MySQL's precedence.

The grammar nests "op bitExpr" to the right, regardless of operator precedence. In "1 + d + INTERVAL 1 DAY", the interval was applied to "d" only, while MySQL applies it to "1 + d". Reject an interval in such a nested operand, and suggest parentheses. The rewritten fold also no longer duplicates translate_sequence(), and the docblock no longer claims that INTERVAL is the only "bitExpr" form that SQLite can't run.

The unit was taken from the token text, so units SQLite doesn't support produced an invalid DATETIME() modifier, and the result was NULL. With the INTERVAL operators, "UPDATE t SET d = d + INTERVAL 1 QUARTER" wrote NULL into the column, while MySQL adds 3 months. The same happened with the SQL_TSI_* synonyms, whose text differs from the unit they map to. Units are now looked up by token type. QUARTER becomes 3 months, and MICROSECOND and the composite units, such as HOUR_MINUTE, are rejected.

MySQL returns a date when it adds only days, weeks, months, quarters, or years to a date. The translation always returned a datetime, so '2024-01-01' + INTERVAL 1 DAY gave '2024-01-02 00:00:00'. SQLite compares these values as strings, so comparisons with DATE values matched the wrong rows. This query also deleted the '2024-01-02' row, which MySQL keeps: DELETE FROM t WHERE d < '2024-01-03' - INTERVAL 1 DAY Dates are stored as 10-character strings, so use DATE() for such inputs. Chained intervals now become a single call with one modifier each, so the date expression isn't repeated for every interval. This fixes the first case of the linked issue. Month ends, ADDDATE(), and SUBDATE() remain open there. #531

## Release `3.1.1` Version bump and changelog update for release `3.1.1`. **Changelog draft:** * Support date arithmetic with `INTERVAL` operators ([#539](#539)) * Fix `ROLLBACK` as the first transaction of a connection ([#537](#537)) * Fix matching a foreign key reference to a unique key with a different case ([#534](#534)) * Fix `FOUND_ROWS()` without `SQL_CALC_FOUND_ROWS` ([#533](#533)) * Fix unique keys and foreign key restore without stringified fetches ([#532](#532)) * Fix `CREATE TEMPORARY TABLE IF NOT EXISTS` and `DROP TEMPORARY TABLE` as the first temporary table ([#524](#524)) * Fix a warning for a foreign key referencing a table with a shorter unique key ([#523](#523)) * Fix temporary table lookup without stringified fetches and with a different case ([#522](#522)) **Full changelog:** v3.1.0...release/v3.1.1 ## Next steps 1. **Review** the changes in this pull request. 2. **Push** any additional edits to this branch (`release/v3.1.1`). 3. **Merge** this pull request to complete the release. Merging will automatically build the plugin ZIP, create a [GitHub release](https://github.com/WordPress/sqlite-database-integration/releases), and deploy to [WordPress.org](https://wordpress.org/plugins/sqlite-database-integration/).

JanJakes mentioned this pull request Oct 19, 2026

Translate date arithmetic written with INTERVAL operators #510

Closed

JanJakes force-pushed the interval-arithmetic branch from 850e0c6 to dd2ddda Compare October 19, 2026 12:07

JanJakes force-pushed the interval-arithmetic branch from dd2ddda to 918410c Compare October 19, 2026 14:39

JanJakes added 3 commits October 20, 2026 10:36

JanJakes force-pushed the interval-arithmetic branch from 918410c to 5b7787c Compare October 20, 2026 08:36

JanJakes marked this pull request as ready for review October 20, 2026 11:12

JanJakes merged commit 0996313 into trunk Oct 21, 2026
25 checks passed

JanJakes deleted the interval-arithmetic branch October 21, 2026 16:12

JanJakes mentioned this pull request Oct 21, 2026

Release 3.1.1 #550

Merged

Nandan108 mentioned this pull request Oct 23, 2026

DATE_ADD() and DATE_SUB() overflow past the end of the month, and ADDDATE() and SUBDATE() are not supported #531

Open

Navigation Menu

Repository navigation

Support date arithmetic with `INTERVAL` operators - #539

Support date arithmetic with `INTERVAL` operators#539
JanJakes merged 5 commits into
trunkfrom
interval-arithmetic

JanJakes commented Oct 19, 2026 •

edited by coderabbitai Bot

Loading

Uh oh!

coderabbitai Bot commented Oct 19, 2026 •

edited

Loading

Sequence Diagram(s)

Uh oh!

Uh oh!

Reviewers

Uh oh!

Assignees

Labels

Projects

Milestone

Development

Uh oh!

2 participants

Conversation

JanJakes commented Oct 19, 2026 • edited by coderabbitai Bot Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Summary

Why the extra fixes

Remaining limitations

Summary by CodeRabbit

Uh oh!

coderabbitai Bot commented Oct 19, 2026 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Sequence Diagram(s)

Uh oh!

Uh oh!

Reviewers

Uh oh!

Assignees

Labels

Projects

Milestone

Development

Uh oh!

2 participants

JanJakes commented Oct 19, 2026 •

edited by coderabbitai Bot

Loading

coderabbitai Bot commented Oct 19, 2026 •

edited

Loading