How do you rewrite WHERE CAST(created_at AS DATE) = DATE '2024-03-01' so it stays sargable?

## The predicate and why it is slow `WHERE CAST(created_at AS DATE) = DATE '2024-03-01'` is the single most common non-sargable predicate in real codebases, and it appears in many spellings — a cast, `DATE(created_at)`, `date_trunc('day', created_at)`, `CONVERT`, `TRUNC`. All of them do the same thing: they wrap the column, so the index on `created_at` — which stores full timestamps in sorted order — has nothing to seek on. The engine reads every row, truncates it, and compares. The query is correct and it is slow, and it stays correct and slow forever, so nothing ever fails except latency. ## The rewrite ```sql WHERE created_at >= DATE '2024-03-01' AND created_at < DATE '2024-03-02' ``` Both boundaries are constants, computed once. The column is bare, so the engine can descend the index to the first entry at or after midnight on the 1st and walk forward until it passes midnight on the 2nd. The predicate is exactly equivalent to the truncated form for a timestamp without time zone: truncating to a date and comparing for equality *is* the statement that the value falls in the half-open interval from that day up to, but not including, the next. The same shape covers every granularity, which is why it is worth learning as a shape rather than as a recipe: ```sql -- one month created_at >= DATE '2024-03-01' AND created_at < DATE '2024-04-01' -- one year created_at >= DATE '2024-01-01' AND created_at < DATE '2025-01-01' -- last 30 days created_at >= CURRENT_DATE - INTERVAL '30' DAY ``` That last one is a reminder of the general rule: an expression on the *constant* side is fine, because it does not mention the column and is evaluated before the scan starts. Only the column's side must stay bare. ## Why not BETWEEN with an end-of-day literal Two classic bugs live here, and interviewers ask about both. First, `created_at BETWEEN DATE '2024-03-01' AND DATE '2024-03-31'` is *sargable but wrong* for a timestamp column. `BETWEEN` is inclusive of both endpoints, but the endpoint is the instant `2024-03-31 00:00:00`, not the whole day. Every row recorded during business hours on the 31st is silently missing. The report looks plausible, the last day is just quietly short. Second, the usual patch — `AND TIMESTAMP '2024-03-31 23:59:59'` — narrows the hole rather than closing it. If the column has fractional-second precision, a row at `23:59:59.500` is still outside. Every increase in precision reopens the bug, and column precision is exactly the sort of thing that changes in a migration nobody connects to this report. The half-open form is immune to both because it never names the last representable instant of the period. It only names the first instant of the *next* period, which always exists and is always exact. ## Time zones If the column is `TIMESTAMP WITH TIME ZONE`, "the 1st of March" is not an absolute interval — it depends on which zone you mean, and casting such a value to a date is a zone-dependent operation. The rewrite is still the right shape, but the boundaries must be the two instants that bracket the day *in the zone the business means*, expressed the way your engine spells that conversion. Engines differ here in both syntax and defaults, so pin it explicitly rather than relying on a session setting; a report whose day boundaries move with the connection's time zone is worse than a slow one. ## Ranges over a computed period When the period comes from a parameter rather than a literal, the same rule applies — compute both boundaries from constants and parameters, and never derive them from the column: ```sql WHERE created_at >= :day AND created_at < :day + INTERVAL '1' DAY ``` This is still sargable: `:day + INTERVAL '1' DAY` mentions a parameter, not the column. Interval literal spelling varies between engines, so check the form yours accepts. ## What to check afterwards After the rewrite, confirm the predicate actually became an index range rather than a leftover filter — the plan should show the bounds attached to the index access, not applied to rows after they are read. And keep the equivalence claim honest in review: the two forms return identical rows, so the rewrite is safe to apply without re-validating the report's numbers, which is exactly what makes it such an easy win.