Why does WHERE created_at BETWEEN '2024-01-01' AND '2024-01-31' miss orders placed on 31 January?
answer
- Think about what time of day the literal means
- The column stores more than a day
- Midnight is the last instant that qualifies
- Prefer a range open at the top: >= start AND < next start
basics
~20 sThe end literal is a date, so it compares as 2024-01-31 00:00:00. A timestamp column keeps only rows at exactly midnight that day; everything later on the 31st fails the <= test. Use a half-open range instead: >= '2024-01-01' AND < '2024-02-01'.
solid answer
~40 s`BETWEEN` is inclusive, but the value it includes at the top is `2024-01-31 00:00:00`, because a date literal compared against a `TIMESTAMP` column carries a zero time-of-day. So an order at 09:15 on the 31st is greater than the upper bound and is filtered out — you silently lose a day per report. The fix is a **half-open range**: `created_at >= DATE '2024-01-01' AND created_at < DATE '2024-02-01'`. That form is correct regardless of the column's time precision, needs no knowledge of fractional seconds, and composes cleanly: consecutive months tile the timeline with no gaps and no double-counting. Avoid the `'2024-01-31 23:59:59'` patch — it loses rows in the last second on a column with fractional-second precision, and it hard-codes an assumption about the type.
code
sql · 8 lines-- wrong: the upper bound is 2024-01-31 00:00:00
SELECT COUNT(*) FROM orders
WHERE created_at BETWEEN DATE '2024-01-01' AND DATE '2024-01-31';
-- right: half-open, correct at any timestamp precision
SELECT COUNT(*) FROM orders
WHERE created_at >= DATE '2024-01-01'
AND created_at < DATE '2024-02-01';go deeper
Know that a bare date literal means midnight, so a BETWEEN whose top bound is a date cuts the final day off a timestamp column. Be able to write the >= start AND < next-start fix.
Explain the implicit promotion of the date literal to a timestamp, why the 23:59:59 workaround fails at fractional-second precision, and why the half-open form is precision-independent.
Bring the reporting consequences: buckets that tile without gaps or overlaps, month-end and leap-year arithmetic you avoid entirely, and how the boundary is defined when the column is timestamp with time zone.
Own it as a convention rather than a fix: half-open ranges everywhere means dashboards, exports and backfills agree on boundaries, and reconciliation differences stop being a recurring investigation.
## The bug ```sql SELECT * FROM orders WHERE created_at BETWEEN DATE '2024-01-01' AND DATE '2024-01-31'; ``` If `created_at` is a `DATE`, this is correct: it returns all 31 days. If `created_at` is a `TIMESTAMP` — which it almost always is for an event column — it is wrong, and wrong in the quietest possible way: the result looks plausible, it is just missing most of the last day. ## Why `BETWEEN` expands to `created_at >= DATE '2024-01-01' AND created_at <= DATE '2024-01-31'`. To compare a `DATE` with a `TIMESTAMP`, the engine promotes the date to a timestamp at the start of that day, i.e. `2024-01-31 00:00:00`. The upper comparison therefore reads: *keep rows at or before midnight on the 31st*. An order created at `2024-01-31 09:15:00` fails it. Only rows landing exactly on midnight survive from the final day. The same thing happens when the literal is written as a string, `'2024-01-31'`, and implicitly cast. Nothing in the query text hints that a day has gone missing; the row count is simply a little low, every time, forever. ## The fix: a half-open range Write the range **closed on the left, open on the right**: ```sql SELECT * FROM orders WHERE created_at >= DATE '2024-01-01' AND created_at < DATE '2024-02-01'; ``` This says "from the first instant of January up to, but not including, the first instant of February", which is precisely what "January's orders" means. Its virtues: - **Type-independent.** It is correct whether the column stores whole days, seconds, milliseconds or microseconds. You never have to know the precision. - **No end-of-month arithmetic.** You compute the *next* period's start, which the calendar gives you directly; you never need to know whether the month ends on the 28th, 30th or 31st, and leap years take care of themselves. - **It tiles.** Successive ranges `[Jan, Feb)`, `[Feb, Mar)` partition the timeline: no instant is counted twice and none is skipped. Two adjacent closed ranges either double-count the boundary or leave a hole in it. - **It reads as one predicate on a bare column**, which keeps the filter in the shape engines can use directly. ## The patch that does not work ```sql -- fragile WHERE created_at BETWEEN '2024-01-01 00:00:00' AND '2024-01-31 23:59:59' ``` On a column with sub-second precision this drops anything in the final second, e.g. `23:59:59.4`. People then escalate to `23:59:59.999`, which drops microsecond values, and so on — the pattern is chasing the precision of a type instead of expressing the range. Similarly, wrapping the column in a truncation function to compare whole days changes the shape of the predicate and is a separate discussion; the half-open bound needs no function on the column at all. ## Time zones If the column is `TIMESTAMP WITH TIME ZONE` and the report means "January in Berlin", the two boundaries are instants derived from a local calendar day, and daylight-saving transitions mean a local day is not always 24 hours. The half-open form is exactly what saves you here too: you compute the instant that starts the period and the instant that starts the next one, and the length between them is whatever the calendar says it is. A closed range needs you to name the last representable instant of a local day, which is not a thing you can write portably. ## When BETWEEN is still fine On a genuine `DATE` column, or on integers and other discrete domains where you can name the last legal value (`WHERE year BETWEEN 2020 AND 2024`), `BETWEEN` is clear and correct — and it is more readable than repeating the column name. The rule of thumb: **`BETWEEN` for discrete domains, half-open comparisons for continuous ones.** Timestamps are continuous for this purpose. ## How to answer Name the cause (date literal promotes to midnight, `<=` therefore cuts the day), give the fix as a half-open range, and volunteer why the `23:59:59` workaround is not the fix. If you add that half-open ranges tile without gaps or overlaps, you have said the thing that makes an interviewer stop asking.
- Why not just write BETWEEN '2024-01-01' AND '2024-01-31 23:59:59'?Because it encodes a guess about the column's precision. On a timestamp with fractional seconds, a row at 23:59:59.4 is greater than 23:59:59 and is still lost. Adding more nines chases the type instead of expressing the range. `< DATE '2024-02-01'` is correct at any precision and needs no such knowledge.
- How do half-open ranges help when you are producing one row per month for a whole year?They tile the timeline. Each bucket is `[month_start, next_month_start)`, so every instant falls into exactly one bucket — no row is counted in two months and none falls in a crack. With closed ranges you must pick the last instant of each month, and any mismatch between buckets shows up as double-counted or missing revenue at the boundary.
- Does the same reasoning apply to a column declared as DATE?No. On a plain `DATE` column, `BETWEEN DATE '2024-01-01' AND DATE '2024-01-31'` is exactly right, because the domain is discrete and the 31st is the last representable value in the range. The trap is specific to columns that carry a time-of-day component.
saying these in an interview costs you the question
- Blames the data instead of the predicate's upper bound
- Patches it with 23:59:59 and calls the range fixed
- Thinks BETWEEN excludes the endpoints, so adds a day to both
- Assumes a date literal compares as end-of-day
- Says every engine casts the literal to cover the whole day