Your timestamps are stored in UTC and your business day runs on local time. Both are correct. The bug lives in the query that joins them, and it is the most ordinary line in your codebase:

->whereDate('published_at', $day)

That asks the database which UTC day a row falls on. Nobody in your organisation works in UTC days. On a live table we compared that query against a timezone-aware one for twenty-one consecutive days, and they disagreed on twelve of them.

How much of your data is even exposed

Only rows whose UTC date differs from their local date can be misfiled, and for a UTC+8 business that means anything timestamped from 16:00 UTC onward — which is the small hours of the next morning locally.

rows with a timestamp                  810
rows where UTC and local dates differ   61   (7.5%)

Seven and a half percent sounds survivable, and that is exactly the trap. The misfiled rows are not spread evenly. They cluster on whichever days the work ran late, turning a correct number into a wrong one.

What the two queries actually returned

day           naive   correct   off by
2026-08-08       19        27       +8
2026-08-07       14         7       −7
2026-08-05        5        11       +6
2026-08-04        6         0       −6
2026-08-20       29        24       −5

On 4 August the naive query confidently reports six items on a day when the correct count was zero. There is no missing-data warning available for that failure, because from the query's point of view nothing is missing. It found six rows. They belong to a different day.

The first row is the same failure pointing the other way: a day with twenty-seven items reported as nineteen, understated by thirty percent.

Why nobody has noticed yet

The reason this bug is so durable is that the errors cancel each other out. Sum both columns over the whole period:

naive total     349
correct total   350

A difference of one, across three weeks. The per-day errors point in both directions and cancel, because a row misfiled out of Tuesday lands in Wednesday and is still counted somewhere.

Every aggregate check you might reasonably write — matching the monthly total, summing the days, watching for drift — still passes. The validation you already have is structurally blind to this, and it will keep reassuring you while more than half your individual days are wrong.

That is worth stating plainly: a correct total is not evidence of correct buckets. If your daily numbers feed a chart, a digest, an alert threshold or an editorial decision, the total is not the thing being used.

Where it surfaces

This is not a hypothetical failure mode for us. A "published today" count reported to the owner was computed on the UTC day, which is how the problem came to be written down in the first place. Nobody spotted it from the number; it was found by someone asking what "today" meant.

That is the characteristic shape of the bug. It never produces an obvious error, a gap, or an implausible figure. It produces a slightly different plausible figure on nearly the right day, and it only comes to light when somebody gets pedantic about what "today" means.

It lands hardest in the outputs nobody validates: a daily digest, a "this week" chart, an alert firing on a per-day threshold. A comparison of today against yesterday is the most exposed of all, because two adjacent buckets that have swapped a few rows read as movement that never happened.

The two fixes, and which to prefer

Convert at the query, either by converting the column or by converting the boundaries:

-- convert the column
WHERE DATE(CONVERT_TZ(published_at, '+00:00', '+08:00')) = ?

-- or convert the day's edges into UTC and use a half-open range
WHERE published_at >= ? AND published_at < ?
      -- local 00:00 and the next local 00:00, both expressed in UTC

We ran both against all twenty-one days and they agreed on every one, so this is not a choice about correctness. Prefer the range. A function wrapped around the column, such as CONVERT_TZ(published_at, …), prevents the database from using an index on it. That quietly turns a lookup into a scan: the answer stays right while the query gets slower as the table grows.

Use the half-open range: >= start and < next_start. Not BETWEEN, which is inclusive at both ends and will double-count anything landing exactly on midnight.

The same mistake in three other places

The query is the visible instance. The rest of the day-boundary family behaves identically and is easier to miss:

"today"         computed from the server's clock, not the business timezone
GROUP BY DATE   the same UTC bucketing, one level further from view
NOW() in SQL    the database's timezone, which is a third setting
date rendering  correct display over incorrectly grouped data

The last is the most subtle. A page can format every timestamp perfectly in local time while the grouping underneath it used UTC — the labels are right, the rows beneath them are wrong, and the page looks entirely correct.

How to check your own

1. count rows whose UTC date differs from their local date
   → your exposure, as a percentage
2. run both queries for the last N days and diff them
   → how many days are actually wrong
3. sum both columns
   → confirm the totals agree, which is why you had not noticed

Step three is not a formality. If your totals disagree you have a different bug as well, and if they agree you have just demonstrated to yourself why the existing checks were never going to catch this one.

How this was measured, and what it does not cover

One live table, 810 rows carrying a timestamp, twenty-one consecutive days, MySQL with the application configured to UTC and the business operating at UTC+8. Every figure is a count from that comparison.

The size of the error depends entirely on your offset and your working hours. At UTC+8 the misfiled window is the local small hours, which is why our exposure is 7.5% rather than something worse. A business at UTC−5 whose busiest hours straddle 19:00 local would find a much larger share of its rows on the wrong side, and the same code would produce a much bigger error.

We did not test a timezone with daylight saving. Singapore has no offset changes, so a fixed +08:00 is exact here. Anywhere with a shifting offset, a fixed number is wrong twice a year and you need a named zone — CONVERT_TZ(col, 'UTC', 'Europe/London') — which in turn requires your database's timezone tables to be loaded, and they frequently are not.

The answer is not to store local time. The column being UTC is what makes the correct query possible at all. The failure is not in the storage; it is in reading a UTC column as though it carried a local day.