✏️ Explanatory Question
WHERE created_at = '2026-08-02' miss rows even though the data exists?Level: Hard — A brutally common production bug that has caused countless "missing data" support tickets.
SELECT * FROM orders WHERE created_at = '2026-08-02' and get 0 rows. But you can clearly see orders placed today when you browse the table. What's happening?
The cause: The created_at column is a DATETIME (or TIMESTAMP), which stores both date AND time — e.g., 2026-08-02 14:30:55. When you compare it to the literal '2026-08-02', MySQL treats that as 2026-08-02 00:00:00 (midnight). So it only matches rows created at exactly midnight — almost never any real row.
DATE(created_at)) or the correct sargable range approach.
| Query | Result | Reason |
|---|---|---|
created_at = '2026-08-02' |
0 rows (usually) | Only matches midnight exactly |
DATE(created_at) = '2026-08-02' |
Correct rows | But index is NOT used (slow) |
created_at >= '2026-08-02' AND < '2026-08-03' |
Correct rows | Uses index (fast & correct) |
WHERE DATE(created_at) = '2026-08-02'>= ... <-- Column: created_at DATETIME e.g. '2026-08-02 14:30:55'
-- THE BUG: matches only midnight -> usually 0 rows
SELECT * FROM orders WHERE created_at = '2026-08-02';
-- WORKS but KILLS the index (function on the column)
SELECT * FROM orders WHERE DATE(created_at) = '2026-08-02';
-- CORRECT & INDEX-FRIENDLY: half-open range
SELECT * FROM orders
WHERE created_at >= '2026-08-02'
AND created_at < '2026-08-03'; -- note: < next day, not BETWEEN
-- Verify the index is used
EXPLAIN SELECT * FROM orders
WHERE created_at >= '2026-08-02' AND created_at < '2026-08-03';BETWEEN '2026-08-02' AND '2026-08-02 23:59:59' can miss rows in the last second (e.g., 23:59:59.500 with fractional seconds). The half-open >= start AND < next_day pattern is bulletproof.
created_date DATE AS (DATE(created_at)) STORED and index it, or store a separate indexed DATE column.