Isn't BETWEEN inclusive on both ends?

It is — the problem isn't BETWEEN's inclusivity, it's what the upper bound value actually equals. BETWEEN x AND '2026-08-31' includes everything up to and including exactly '2026-08-31 00:00:00'. Any row timestamped later that same day (08:15am, 3pm, 11:59pm) is after that instant and falls outside the range, even though it's still August 31st.

Is this different from the timezone issue in defining 'this week'?

Related but distinct. The timezone trap is about which calendar day a timestamp falls on depending on which timezone you're using to read it. This is about a date literal implicitly meaning midnight regardless of timezone — even with timezone handled correctly, an inclusive BETWEEN on a plain date still cuts off before the day ends.

Does this happen with DATE columns too, or just TIMESTAMP?

It's specific to TIMESTAMP (or TIMESTAMPTZ) columns being compared against date literals. A true DATE column has no time component at all, so '2026-08-31' as a DATE genuinely represents the whole day and BETWEEN works as expected. The bug is really about comparing a timestamp (which has a time-of-day) against a date literal (which doesn't).

What's the actual fix?

Use a half-open range with = '2026-08-01' AND created_at < '2026-09-01'. That version includes every moment of August 31st, no matter what time each row happened.

Why not just add 23:59:59 to the upper bound instead?

It works for most cases but has its own edge: it drops any fractional seconds (23:59:59.999999 vs 23:59:59), and it's an easy detail to forget or copy wrong. The < (next day) pattern is simpler to get right every time and doesn't depend on knowing the column's precision.

BETWEEN Dates Misses Last Day SQL | AI Database Bug

Q: Why not just add 23:59:59 to the upper bound instead?

It works for most cases but has its own edge: it drops any fractional seconds (23:59:59.999999 vs 23:59:59), and it's an easy detail to forget or copy wrong. The < (next day) pattern is simpler to get right every time and doesn't depend on knowing the column's precision.

You ask for "total orders in August." The number comes back a little low. You check manually — there are definitely orders from August 31st in the system. They're just not in the result, and nothing about the query looks wrong at a glance.

The likely cause: BETWEEN '2026-08-01' AND '2026-08-31' on a timestamp column doesn't mean "through the end of August 31st." It means up to exactly midnight on the 31st — which cuts off every order placed any time after 12:00am that day.

What the upper bound actually equals

BETWEEN is genuinely inclusive on both ends — that part works exactly as advertised. The issue is what value the upper bound resolves to. A bare date literal like '2026-08-31', compared against a timestamp column, is implicitly '2026-08-31 00:00:00'. So the range BETWEEN '2026-08-01' AND '2026-08-31' really means:

'2026-08-01 00:00:00'  through  '2026-08-31 00:00:00'

An order placed at 9am on August 31st has a timestamp of '2026-08-31 09:00:00' — which is after '2026-08-31 00:00:00', and therefore outside the range. The entire last day of the period, except its very first instant, gets silently excluded.

Why this is a different bug than the timezone one

If you've read about timezone traps in "this week" queries, this can look like the same category of problem — it isn't, quite. The timezone trap is about which calendar day a UTC-stored timestamp falls on once you convert it to a local timezone. This bug shows up even with timezone handled perfectly correctly — it's purely about a date literal implicitly meaning "midnight, no time component," regardless of what timezone you're reasoning in. You can fix the timezone entirely and still lose August 31st to this.

It's also specific to timestamp (or timestamptz) columns compared against date literals. A genuine DATE column has no time-of-day at all, so '2026-08-31' as a DATE really does represent the whole day, and BETWEEN behaves the way you'd naturally expect. The bug is really about mixing a timestamp (which carries a time) with a date literal (which doesn't).

The fix: half-open range with `<`, not `BETWEEN`

Instead of naming the last day as an upper bound, name the day after the period ends and use <:

-- misses everything after midnight on Aug 31
WHERE created_at BETWEEN '2026-08-01' AND '2026-08-31'

-- includes all of August, every second of the 31st
WHERE created_at >= '2026-08-01' AND created_at < '2026-09-01'

This pattern — sometimes called a half-open interval — is the standard fix for date ranges over timestamp columns specifically because it doesn't depend on knowing how much precision the column stores. Appending 23:59:59 to the upper bound instead works in most cases but drops any fractional-second precision the column might have, and is one more detail to get right (and remember to get right) every single time you write a range.

How to catch it

If a count for "this month" or "this week" looks slightly low, check specifically whether it's missing data from the last day of the period — that specific pattern (everything present except the tail end of the range) is close to a signature for this exact bug, distinct from a more evenly-scattered undercount you'd see from a different kind of filter issue.

The prompt that heads this off

When filtering a timestamp column by a date range, don't use BETWEEN
with the last day as the upper bound — that implicitly means midnight
of that day and cuts off the rest of it. Use >= start_date AND <
(day after the range ends) instead, so the entire last day is included.

The short version

BETWEEN isn't broken, and the range isn't wrong in spirit — the bug is that a bare date, compared against a column that also stores time-of-day, means the very first instant of that date, not the whole day. "In August" needs to reach all the way to September 1st to actually mean all of August.

Read-only MCP gateway: mcpserver.design.

Related reading: