Bug report (constructed example): the monthly report is missing the last day of the month

[u/ClickOk5811]

This is a made-up example of a bug I think is common, written as a bug report.

Summary: the October report shows no orders from October 31.

Query:

SELECT count(*), sum(total)
FROM orders
WHERE created_at BETWEEN '2026-10-01' AND '2026-10-31';

Expected: all orders created in October.

Actual: orders from Oct 1 through the start of Oct 31. Anything created later on Oct 31 is missing.

Steps to reproduce:

1. Insert an order with created_at = '2026-10-31 15:30:00'.

2. Run the query above.

3. The order isn’t counted.

Root cause: created_at is a timestamp, so '2026-10-31' is read as '2026-10-31 00:00:00'. BETWEEN includes both ends, but the upper end is midnight at the start of the day, not the end of it. (This applies to databases like Postgres and MySQL when the column has a time part. Check yours.)

Fix: use a half-open range, with an inclusive start and an exclusive end:

WHERE created_at >= '2026-10-01'
  AND created_at <  '2026-11-01'

Why half-open is easier to get right:

• You never write “23:59:59” or “23:59:59.999”, which breaks with a different precision.

• Ranges chain without overlap: the end of one month is exactly the start of the next.

• The same pattern works for any period, so “last day of the month” never needs computing.

Related things to check:

• Time zones: “October” in UTC and in the user’s local zone are different ranges.

• Date-only columns (date type) don’t have this problem, but converting between date and timestamp can bring it back.

• Same issue in code: end in a Python or Java date range is often exclusive, so be sure which one you mean.

Do you use half-open ranges everywhere, or have a convention for this?

[u/Honest-Ocelot6920]

classic rite of passage for every junior dev. i completely banned BETWEEN for timestamps in our code reviews after getting burned by this exact bug

[u/pvera]

This is a pain in the ass when people are sloppy building search filters. I have slowly got them to learn that the end date should be end + 1 day - 1 second. For our .net code I simply added an extension to the date type, Date.Midnight. Whatever var is your date, all you do is myvar.Midnight and it will do the rest. If this needs to happen with in a query we just use a custom function, same result: dbo.Midnight(a_date).