Date ranges in SQL without losing a day: half-open intervals

The September report was two percent short. Nobody could find a bug. The query was BETWEEN '2026-09-01' AND '2026-09-30' on a timestamp column, and September 30 after midnight was not in it. This page is only about range boundaries: why a date is an instant, the half-open interval, DATE_TRUNC to build the edges, and the time zone hiding in every midnight. The date functions themselves are on the SQL dates sheet. The print-ready A4 PDF is at the bottom.

The problem

• '2026-09-30' as a timestamp means 2026-09-30 00:00:00.
• <= end_date therefore keeps the first instant of the 30th and nothing after. The 30th is not in the month.

Half-open

• >= start: the first instant, included.
• < '2026-10-01': an exclusive end. There is no last day to lose.
• DATE_TRUNC plus INTERVAL: the start of the month, plus one month, is the end.

The same month, two ways

A timestamp boundary is an instant. '2026-09-30' is the first instant of the 30th, so BETWEEN keeps none of that day.

Loses September 30:

SELECT COUNT(*)
FROM orders
WHERE created
  BETWEEN '2026-09-01'
      AND '2026-09-30';
-- stops at 00:00 on the 30th

Half-open:

SELECT COUNT(*)
FROM orders
WHERE created >= '2026-09-01'
  AND created < '2026-10-01';
-- every second of September

For the current month, let the database compute the edges:

SELECT COUNT(*)
FROM orders
WHERE created_at >= DATE_TRUNC('month', CURRENT_DATE)
  AND created_at <  DATE_TRUNC('month', CURRENT_DATE) + INTERVAL '1 month';

Remember

• Time zone: midnight where? Say it in the query.
• DATE columns: BETWEEN is fine, there is no time of day to lose.
• Adjacent ranges: the end of one is the start of the next.

The trap: the boundary that moves

Three boundaries that look right and count the wrong rows.

Adjacent ranges share a boundary: the end of September is the start of October, and the same instant is counted exactly once. Sum the twelve monthly counts and you get the yearly count, which is the test that the boundaries are right.

The time zone, in one query

On a timestamptz column, a bare literal is read in the session time zone. On a server that is usually UTC, so "September" starts at 02:00 Paris time and the orders between midnight and two in the morning on the 1st fall into August. In PostgreSQL, name the zone in the literal or in the truncation:

SELECT COUNT(*)
FROM orders
WHERE created_at >= TIMESTAMPTZ '2026-09-01 00:00 Europe/Paris'
  AND created_at <  TIMESTAMPTZ '2026-10-01 00:00 Europe/Paris';

The rule is not "always use Paris". It is: the report has a time zone, write it down, and build the boundaries in that zone.

The full page is a free print-ready A4 PDF: half-open ranges, DATE_TRUNC, the time zone.