Skip to main content

Test your SQL date filter with four boundary rows

Updated 3 min readMarkdown

A December returns report should include a return accepted at the very end of December 31. It should exclude a return accepted at midnight on January 1. Those two rows are enough to expose a date filter that looks right on ordinary daytime data.

Before checking the full report, try four deliberately chosen timestamps: just before the month, at its start, near its end, and at the start of the next month. Here is a complete PostgreSQL query; it creates a temporary result set with VALUES, so you do not need a table.

WITH returns (return_id, accepted_at) AS (
  VALUES
    (701, TIMESTAMP '2026-11-30 23:59:59.999999'),
    (702, TIMESTAMP '2026-12-01 00:00:00'),
    (705, TIMESTAMP '2026-12-31 23:59:59.999999'),
    (706, TIMESTAMP '2027-01-01 00:00:00')
)
SELECT
  return_id,
  accepted_at BETWEEN TIMESTAMP '2026-12-01'
                  AND TIMESTAMP '2026-12-31' AS date_end,
  accepted_at >= TIMESTAMP '2026-12-01'
    AND accepted_at <= TIMESTAMP '2026-12-31 23:59:59' AS last_second,
  accepted_at >= TIMESTAMP '2026-12-01'
    AND accepted_at < TIMESTAMP '2027-01-01' AS next_month
FROM returns
ORDER BY return_id;

Each Boolean column answers: would this version of the filter keep the row?

return_id date_end last_second next_month
701 false false false
702 true true true
705 false false true
706 false false false

BETWEEN includes both endpoints. In date_end, the upper endpoint is midnight at the start of December 31, so the return near the end of that day falls outside it. Changing the endpoint to 23:59:59 still misses row 705: PostgreSQL timestamps can contain fractional seconds.

The third filter uses the start of January as an exclusive endpoint. It keeps the December return without needing to name the last possible instant of December:

WHERE accepted_at >= TIMESTAMP '2026-12-01'
  AND accepted_at <  TIMESTAMP '2027-01-01'

January can then begin with accepted_at >= TIMESTAMP '2027-01-01'. The row at exactly midnight belongs to January, and the two monthly ranges do not overlap. This is a half-open interval: the start is included and the end is excluded.

These examples use timestamp without time zone. If your report uses timestamptz, first define which business time zone determines the month and construct the boundaries for that zone. Changing an operator does not settle the time-zone decision.

For a real report, keep the four boundary cases alongside your usual sample data and check the actual row IDs. A plausible total can hide an excluded row or a row assigned to the wrong period.

You can try the date filters in SQLBrief. The browser editor has the data ready: compare the filters, then write a December query against a larger returns table. No installation or account is needed.

PostgreSQL reference: comparison predicates, including BETWEEN and date/time types.

About the author

  • I'm Aleks, building SQLBrief. I write about SQL mistakes that change the meaning of a report, with examples you can run yourself. SQLBrief offers browser-based SQL practice in English and Russian.

No comments yet. Be the first to comment.