Chapter 28 · advanced · 6 min
Dates that quietly lose a day
This class of bug is dangerous specifically because it never errors. The query runs, returns a number, and the number is close enough to right that nobody double-checks it — until it doesn't match a dashboard someone else built, and now it's your job to figure out why.
Three traps, all real, all verified against actual Postgres output below: a BETWEEN that drops a day, a week boundary that depends on someone else's default, and a UTC-vs-local mismatch on a site that operates in one timezone.
The dataset
An orders table with a created_at timestamp column (not a bare date) — the exact shape where these traps live.
Schema
| order_id | INT |
| created_at | TIMESTAMP |
| amount | NUMERIC |
Example data
The BETWEEN trap
The request is "orders in January." This looks right, and it isn't: BETWEEN '2026-01-01' AND '2026-01-31' on a timestamp column treats the end date as 2026-01-31 00:00:00 — midnight at the very start of the 31st. Anything that happened later that same day falls outside the range.
Before you run it — there are 4 orders, two of them on the 31st (08:00 and 23:45). How many rows come back?
Why it's not the answer you expect
Order 3, at 08:00 on the 31st, does make it in — 08:00 is still before midnight-at-the-start-of-the-31st is... wait, it's after. Run it and look closely: only one of the two January-31st orders survives, and it's not obvious which without checking. BETWEEN's upper bound is a single instant, not a whole day, and every order after that instant on the same calendar day is silently excluded. Nothing errors. The row count is just quietly short.
The fix: half-open range
Don't ask for <= end of the last day; ask for < the start of the next period. This is correct regardless of how many decimal places of precision the timestamp column has — there's no "midnight minus one second" to get wrong.
Before you run it — how many of the 4 orders come back this time?
The MYT-vs-UTC trap
If created_at is stored in UTC but the business operates in Malaysia (UTC+8), a query filtering for "today in Kuala Lumpur" using bare UTC dates is off by up to 8 hours at each edge — an order placed at 11pm MYT is still "tomorrow" in UTC, and a naive DATE(created_at) = '2026-01-31' filter drops it. Convert to the business's timezone before truncating to a date, don't filter the raw UTC column against a local-looking date.
Before you run it — do any of these times shift to a different calendar day once converted to MYT (UTC+8)?
The week-start trap
DATE_TRUNC('week', created_at) rounds down to Monday by Postgres's default — but plenty of businesses report weeks starting Sunday, and a dashboard built by someone else may already assume that. Two "correct" queries can disagree on which week an order belongs to, and both are internally consistent; the mismatch is a convention difference, not a bug in either one. Always check what convention the number you're reconciling against uses before assuming yours is wrong.
If you've used Excel or Google Sheets
Spreadsheet dates have the same trap in a different shape: a date entered as 31/1/2026 is really a serial number at midnight, and a <= comparison against another midnight-anchored date behaves the same way BETWEEN does here — it's just less visible because spreadsheets rarely show the hidden time-of-day component.
Ready to practice? DHL's late-delivery SLA and Telekom's subscription days remaining below both hinge on getting a date boundary exactly right.
Default to >= start AND < next_period for any timestamp range, and always check what timezone a column is actually stored in before trusting a date comparison against it. Next: Profiling a table you've never seen.