BETWEEN with Timestamps

1. Why this happens

BETWEEN on a date column looks like the obvious way to ask for "everything in January" — pick the first of the month, pick the last, done. It works perfectly when the column only ever stores a bare date. The moment that column stores a full timestamp instead, the same query starts silently dropping every order placed on the last day of the range after midnight, and nothing about the query looks wrong. It runs, it returns rows, the totals just quietly don't add up.

2. The dataset

Verified by actually running this against SQLite — not hand-computed. Six sales, five of them genuinely in January, stored with full 'YYYY-MM-DD HH:MM:SS' timestamps — including two on January 31st, one first thing in the morning and one right before midnight.

CREATE TABLE sales(
  sale_id INTEGER,
  product TEXT,
  amount REAL,
  order_time TEXT
);

INSERT INTO sales VALUES
 (5,'Widget',120,'2026-01-01 00:00:00'),
 (1,'Widget',100,'2026-01-05 10:00:00'),
 (2,'Gadget',200,'2026-01-15 14:30:00'),
 (3,'Widget',150,'2026-01-31 09:00:00'),
 (4,'Gadget',300,'2026-01-31 23:45:00'),
 (6,'Gadget',250,'2026-02-01 08:00:00');

Five of these six rows belong in a January report. Only the last one, from February 1st, should be excluded.

3. The bug

"Every sale in January" reaches for the two boundary dates directly:

SELECT *
FROM sales
WHERE order_time BETWEEN '2026-01-01' AND '2026-01-31'
ORDER BY order_time;

Three rows come back, totaling ₹420. Both January 31st sales are missing — ₹450 of real revenue, more than half the month's actual total of ₹870, gone with no error and no obviously wrong row count.

4. Why it happens

BETWEEN x AND y is shorthand for column >= x AND column <= y — nothing more. The upper bound, '2026-01-31', is a five-character-shorter string than '2026-01-31 09:00:00'. SQLite compares text lexicographically, and a string that starts with the same characters but then continues sorts after the shorter one — the same rule that puts '2026-01-31 anything' after '2026-01-31' puts a timestamp of any time on the 31st after the bare date. order_time <= '2026-01-31' is only satisfied by rows at exactly midnight, 00:00:00, on the 31st. Every other second of that entire day fails the comparison.

5. Fix one: a half-open range

SELECT *
FROM sales
WHERE order_time >= '2026-01-01'
  AND order_time <  '2026-02-01'
ORDER BY order_time;

Instead of trying to name the last possible instant of January, the upper bound becomes the first instant it's no longer January — the start of February — with a strict <. Every timestamp on the 31st, no matter the time, is still less than '2026-02-01', so all five real January rows now come back. Revenue is correctly ₹870. This pattern — start of the range inclusive, start of the next range exclusive — works regardless of what time component a row happens to carry, because it never tries to guess where a period ends.

6. Fix two: strip the time component before comparing

SELECT *
FROM sales
WHERE date(order_time) BETWEEN '2026-01-01' AND '2026-01-31'
ORDER BY order_time;

SQLite's date() function truncates a timestamp down to just its date portion, so date('2026-01-31 23:45:00') becomes '2026-01-31' — now an exact match against the upper bound instead of a string that sorts past it. This also correctly returns all five January rows and ₹870. The original BETWEEN shape is preserved; only the column being compared changes, which makes this the smaller diff if BETWEEN column1 AND column2 is already used all over an existing codebase.

7. When plain BETWEEN on dates is fine

None of this is an argument against BETWEEN itself — it's an argument about what's actually stored in the column being compared. If a column is a genuine date-only value that never carries a time component — an order_date column that's always exactly '2026-01-31' and nothing else — the original, simplest form of BETWEEN '2026-01-01' AND '2026-01-31' is completely correct, because there's no hidden time-of-day for the upper bound to accidentally exclude. The trap only exists at the boundary between a date-shaped comparison and a column that can hold more precision than the comparison expects.

8. Cheat sheet

9. Try it yourself

This one is easy to carry from one project to the next without ever noticing, because it only breaks on a column that stores more precision than the query expects — and it breaks by quietly deleting the last day of whatever range is being reported on, every single time. Before shipping a BETWEEN on any date-like column, it's worth checking what that column actually holds: a pure date, or a timestamp wearing a date's clothes.

Practice date filtering live on TableNotFound →