The workflow at a glance
This example means October 2026 in UTC, with a PostgreSQL timestamp-with-time-zone column. Use a lower bound that includes October 1 midnight and an upper bound that excludes November 1 midnight, then compare expected IDs. The fixture and reference interpretation are editorial examples, not measured model output or a claim of execution in your database.
Write the contract before AI supplies a time zone
The contract has three conditions: occurred_at is a timestamp with time zone; the month is defined in UTC; missing timestamps are reported separately rather than assigned a month. If your business closes months in New York, London or another local zone, derive its boundaries separately instead of substituting these UTC values.
The PostgreSQL date/time type reference distinguishes timestamps with and without time zones. A +00 in the string alone does not establish the column type. If imported dates are ambiguous, preserve original values and offset evidence using the AI date-normalization check before auditing the filter.
Use five rows to expose the assumptions
Every row below is an original editorial test case, not user data. Write the expected answer before inspecting the SQL. Otherwise, it is easy to derive the answer from the very query you are supposed to check.
| ID | occurred_at | Expected treatment |
|---|---|---|
| A | 2026-09-30 23:59:59+00 | Exclude: before October |
| B | 2026-10-01 00:00:00+00 | Include: exactly at the start |
| C | 2026-10-31 23:59:59.999+00 | Include: still October |
| D | 2026-11-01 00:00:00+00 | Exclude: already November |
| E | NULL | Report separately: missing timestamp |
Give each boundary an explicit meaning
SELECT id
FROM events
WHERE occurred_at >= TIMESTAMPTZ '2026-10-01 00:00:00+00'
AND occurred_at < TIMESTAMPTZ '2026-11-01 00:00:00+00'
ORDER BY id;This filter does not also validate source completeness, duplicate records, table joins or the definition of a business metric.
Two plausible-looking filters fail
The PostgreSQL comparison reference defines BETWEEN as inclusive at both endpoints; ordinary NULL comparisons cannot be treated as genuine timestamps either. Using November 1 as an inclusive upper bound adds D. Ending at midnight on October 31 instead loses records later that day, including C.
Three reference results from the same fixture
BETWEEN the start and November 1 returns B, C and D: three rows. An upper bound at October 31 midnight leaves only B in this fixture.
The start-inclusive, next-month-exclusive interval returns B and C: two rows. E remains in the missing-timestamp record.
Include verification in the AI request
Review the month filter without executing database operations.
Database: PostgreSQL. Column occurred_at: timestamptz. Business month: October 2026 in UTC.
Include the start; exclude the next month’s start. Report NULL separately without filling midnight.
Classify A through E first, then list expected IDs and count. Explain BETWEEN endpoints and type assumptions.
Give a query verifiable on isolated test data. Do not claim it has run or passed.
Insert this article’s five rows and the candidate SQL here.Two returned rows are not the whole acceptance test
Compare the actual B and C IDs, explain why A and D are excluded, and account for E separately. Looking only at a count can hide an incorrect record replacing a correct one. As with the sample-row audit for AI spreadsheet formulas, establish the reference independently of the generated result.
Avoid pretending that 23:59:59 represents every possible end-of-day timestamp; data may include fractional seconds. Excluding the next month’s start is easier to explain, but still requires a defined zone. Duplicate IDs, daylight-saving changes and business cutoff rules need additional fixtures; this article has not validated those cases.
Acceptance criteria for this case
- Database, column type and business time zone are explicit.
- B and C are included, A and D excluded, and E remains missing.
- The ID set is checked, rather than only the number of rows.
- Isolated boundary tests precede an authorized review of the real query.
Common questions
Is BETWEEN always wrong for dates?
No. It includes both endpoints, which can match some tasks. A start-inclusive, next-month-exclusive interval is often easier to explain for monthly partitions; the task definition decides.
Why not end at 23:59:59?
Real timestamps may contain fractional seconds. Excluding the next month’s start avoids guessing the precision of the last possible second.
Can a New York business month copy the UTC bounds?
No. Derive the start and end in the actual business zone, then check types and conversion. This fixture defines only a UTC month.
