Common mistake

SQL: Why a Date Range Drops the Last Day

A timestamp compared to a bare date excludes that day's times. Use a half-open range instead of BETWEEN.

The symptom

You ask for orders placed from 1 January through 30 January and write it the obvious way, with BETWEEN '2024-01-01' AND '2024-01-30'. The total comes back a little low. The range looks inclusive on both ends, yet the last day's rows are missing.

Why it happens

When the column holds a timestamp (a date and a time), the bare date '2024-01-30' is read as2024-01-30 00:00:00, which is midnight at the start of that day. BETWEEN is inclusive of that instant and nothing after it, so an order placed at any point during the 30th falls outside the range. The upper bound is inclusive of a moment almost nobody has data at.

The fix

Use a half-open range: >= the start and< the day after the end. It captures every time on the last day and never overlaps the next period, and the same shape works for days, months, and years. Both queries below count Garden Shop orders placed from 1 January through 30 January.

Wrong: BETWEEN drops the last day

with order_events as (
  select order_id,
         cast(order_date as timestamp) + interval '12 hours' as order_ts
  from orders
)
select count(*) as orders_in_range
from order_events
where order_ts between timestamp '2024-01-01' and timestamp '2024-01-30'

Right: half-open range

with order_events as (
  select order_id,
         cast(order_date as timestamp) + interval '12 hours' as order_ts
  from orders
)
select count(*) as orders_in_range
from order_events
where order_ts >= timestamp '2024-01-01'
  and order_ts <  timestamp '2024-01-31'

Garden Shop's order_date is a pure date, so the bench casts it to a timestamp at noon to stand in for the real thing: an order placed midday on 30 January. That is the order BETWEEN loses. If your column really is a bare DATE with no time component,BETWEEN is safe, but the half-open habit keeps you correct either way.

Another option

When the range is a whole calendar period you can truncate instead, e.g.where date_trunc('month', order_ts) = timestamp '2024-01-01'. It reads clearly, but wrapping the column in a function can stop the database from using an index on it, so the half-open range is usually the safer default on large tables.