Query a day with a half-open timestamp range
A daily report uses an end time of 23:59:59 and misses rows with fractional seconds after that instant. A half-open range includes the start and excludes the next day's start, avoiding a guessed final microsecond.
Use the next boundary
SELECT id, created_at
FROM orders
WHERE created_at >= '2026-10-01 00:00:00'
AND created_at < '2026-10-02 00:00:00';
These sample timestamps assume the stored timezone convention matches the boundaries. For a customer's local day, compute both local midnight boundaries in that timezone and convert them to the storage timezone before binding them.
Keep the indexed column usable
Wrapping every row's timestamp in DATE(created_at) may prevent an ordinary index from serving the range as intended. A direct range on the column is often a better starting point. Confirm with the actual query plan rather than assuming a specific performance gain.
Daylight-saving transitions mean a local day is not always 24 elapsed hours. Compute the next calendar boundary instead of adding a fixed number of seconds when local-day reporting is the requirement.
Test the exact start, a value just before it, the final fractional second, and the exact next-day start. Reuse the same boundary helper for the report total and its detail list. Otherwise the number at the top of a dashboard can disagree with the rows users open beneath it.