DATETIME(n) / TIMESTAMP(n) columns drop fractional seconds #527

Reproduction

CREATE TABLE d (id INT, ts DATETIME(6));
INSERT INTO d (id, ts) VALUES (1, '2026-06-22 07:16:00.123456');
SELECT ts FROM d;
-- '2026-06-22 07:16:00'   (MySQL: '2026-06-22 07:16:00.123456')
SELECT COUNT(*) FROM d WHERE ts = '2026-06-22 07:16:00.123456';
-- 0                       (MySQL: 1)

Reproduced on trunk (3.1.0) with PHP 8.3 and SQLite 3.45.1.

Values written to DATETIME/TIMESTAMP columns are cast through SQLite's DATETIME(), which has no fractional part, so the column's declared precision is ignored. A value read back loses its fraction, and an equality or IN comparison against the value as written matches nothing. For example, an outbox that deletes processed rows by (id, queued_at) never deletes them.

Relation to #508

#508 adds MySQL-compatible date/time parsing and formatting, including fractional seconds, in the same code paths. Would you prefer this fixed on top of #508 (honoring DATETIME_PRECISION when casting on write, with MySQL's rounding or TIME_TRUNCATE_FRACTIONAL), or separately? I'm happy to contribute a PR in whichever direction fits.

Found while running WooCommerce on WordPress Playground.

Use of AI

This report was prepared with Claude Code.