Is this the same as BETWEEN cutting off the last day?

Same family, sharper failure. BETWEEN on dates drops the tail of the last day. Equality to a date drops the entire day except the first instant. You get zero rows, not a slightly short count.

Does CAST(created_at AS date) = CURRENT_DATE fix it?

It includes the whole calendar day in the session timezone. It also wraps the column, which usually cannot use an index on created_at. A range (>= start AND < next day) is the version that stays correct and stays fast.

Why do some rows still match?

Only rows stored at exactly midnight, or rows whose column is a date type with no time. A timestamptz column almost never equals a bare date.

CURRENT_DATE is the database session's date, often UTC. 'Today' in Bangkok can be yesterday in UTC for part of the evening. Name the timezone, then build the range from that date.

MySQL DATE(created_at) = CURDATE() — same bug?

DATE() on the column includes the day, so the count can be right. The equality without DATE() (created_at = CURDATE()) is the zero-row version. Prefer a range either way.

Timestamp Equals Date SQL | CURRENT_DATE

Q: Why do some rows still match?

Only rows stored at exactly midnight, or rows whose column is a date type with no time. A timestamptz column almost never equals a bare date.

Q: What about timezone?

CURRENT_DATE is the database session's date, often UTC. 'Today' in Bangkok can be yesterday in UTC for part of the evening. Name the timezone, then build the range from that date.

Q: MySQL DATE(created_at) = CURDATE() — same bug?

DATE() on the column includes the day, so the count can be right. The equality without DATE() (created_at = CURDATE()) is the zero-row version. Prefer a range either way.

You ask for "orders today." The AI comes back with zero. The dashboard shows dozens. The SQL has no join and no NULL trap. It compared a timestamp to a date with =.

A date is midnight, not a day

-- matches only 2026-09-29 00:00:00
WHERE created_at = CURRENT_DATE
WHERE created_at = DATE '2026-09-29'
WHERE created_at = '2026-09-29'

created_at stores a time. A bare date, in that comparison, is the first instant of that date. An order at 09:14 does not equal midnight. The query is valid. The result is empty. That looks like "we had no orders," which is worse than a slightly wrong total — people believe a zero.

-- the whole local day, half-open
WHERE created_at >= DATE '2026-09-29'
  AND created_at <  DATE '2026-09-29' + INTERVAL '1 day'

This is the zero-row cousin of BETWEEN missing the last day. That one is a short range. This one is no range at all. EXTRACT(MONTH) is the opposite mistake: too many years. "Today" needs one window, not an equals sign.

CURRENT_DATE is also the session's date. If the server is UTC and "today" means Bangkok, you can still be a day off after the equality is fixed — that's the timezone trap.

How to catch it

If a "today" or "on this date" question returns 0:

Show the WHERE. If created_at is compared with = to a date
or to CURRENT_DATE, rewrite it as >= that date AND < the next day.
Then show COUNT(*) and MIN/MAX(created_at) on that range.

A MIN that is hours after midnight, with a count that matches the dashboard, means the equality was the bug.

The prompt that heads this off

Never compare a timestamp column to a date with =.
"Today" and "on DATE" mean >= start of that day AND < start of the next day.
Say which timezone "today" is in. Show MIN and MAX of the filtered timestamps.

The short version

created_at = '2026-09-29' means "exactly midnight." "Orders today" means every timestamp on that day. A confident zero is the tell.

Read-only MCP gateway: mcpserver.design.

Related reading: