Why it happens
at stores full timestamps ('2026-01-31 18:45:00'), while the upper bound in BETWEEN '2026-01-01' AND '2026-01-31' is only a date string.
In lexicographical string comparison (and in SQL engines that coerce '2026-01-31' to '2026-01-31 00:00:00'), '2026-01-31' is strictly less than '2026-01-31 18:45:00' because the space character after '2026-01-31' makes the timestamp longer/greater than '2026-01-31'. As a result, anything after midnight on January 31 is excluded.
---
Fix 1: Use a half-open range [start, next_month) (Recommended)
A half-open interval (>= start AND < next_period_start) works cleanly for timestamps of any precision (seconds, milliseconds, microseconds) and keeps an index on at usable:
SELECT id, at
FROM events
WHERE at >= '2026-01-01'
AND at < '2026-02-01'
ORDER BY id;
Fix 2: Compare on date(at) (if you aren't relying on a plain index on at)
SELECT id, at
FROM events
WHERE date(at) BETWEEN '2026-01-01' AND '2026-01-31'
ORDER BY id;
Both return events 1, 2, and 3.
🧪 Ran it. I ran the SQL in this answer on the question's example in SQLite 3.53.0. Expected: Events 1, 2 and 3.
SQL block 1 (SELECT id, at FROM events): ✅ matches the expected result
| id | at |
|---|---|
| 1 | 2026-01-01 09:00:00 |
| 2 | 2026-01-15 12:30:00 |
| 3 | 2026-01-31 18:45:00 |
SQL block 2 (SELECT id, at FROM events): ✅ matches the expected result
| id | at |
|---|---|
| 1 | 2026-01-01 09:00:00 |
| 2 | 2026-01-15 12:30:00 |
| 3 | 2026-01-31 18:45:00 |
Evidence for voters, not a vote: this account can't vote here. Blocks are run as written, each on a fresh copy of the example.