Half-open date ranges and declared storage

Date filtering requires a declared representation and business interval. This shared fixture stores normalized ISO calendar dates as text, all in the same format. Lexical comparisons therefore work for these date-only values. Real timestamps need a native temporal type and an explicit timezone policy.

The worked interval starts on 2 October and ends before 4 October. It returns identities 3, 4 and 5. Naming the exclusive end avoids inventing a last millisecond and transfers naturally to adjacent reporting periods without overlap.

A half-open range uses value greater than or equal to start and strictly less than end. The same end becomes the next interval's start. This supports clean partitioning of a timeline when both filters use the same temporal representation and population.

The teaching query compares the stored column directly with two constants. In production, bind appropriately typed boundary values. Converting every row through a function can affect index use, while silently mixing offsets or formats can affect correctness before performance is even considered.

The counterexample uses BETWEEN with the same end date. BETWEEN includes both boundaries, so records published on 4 October also enter the result. That answers a different interval and creates overlap if the next report begins on the fourth.

Do not extrapolate the fixture's text comparison to arbitrary date strings or timestamp strings with mixed offsets. An apparent order such as month/day/year text is not chronological. Choose the storage type and timezone first, then derive boundaries in that same domain.

Find active roles published during the single calendar date 4 October. Use an inclusive 4 October start and an exclusive 5 October end. Predict identities 6 and 8; the same date also contains archived role 7, which must remain excluded.

Write the temporal interval and active requirement independently, then combine them with AND. Do not use a made-up 23:59:59 endpoint. The fixture has date-only values, but practicing the half-open structure prepares you for timestamp precision without depending on it.

Step 1: Choose the calendar day boundaries. Step 2: Combine the interval with active status.

The solution returns exactly 6 and 8. Identity 7 is removed by status rather than by the date boundary. The acceptance check therefore exercises both the interval and the population. Adjacent day reports can reuse the same boundary rule without counting a boundary record twice.

For a real timezone-based business day, compute the corresponding start and next-day boundary using the relevant timezone rules. Some days differ from twenty-four hours. The simple date-only lab makes no claim to execute or validate daylight-saving conversions.

In an interview, distinguish calendar dates, local timestamps and instants. Ask which timezone defines a business day and whether the stored type already represents an instant. This is more useful than reciting one database function without identifying the temporal contract.

The worked range is portable because its ISO text storage is explicit. Engine-specific timestamp arithmetic belongs in a declared dialect with dedicated fixtures. You will reuse these interval boundaries in reports, lag calculations and calendar-completeness checks later in the analytics track.

Optional stretch — after the core task works: Describe a timezone-aware business-day interval without assuming it always lasts twenty-four hours.

Computer practice: https://applydjinn.com/typorithm/sql-lab-20261105.zip. Load the shared setup.sql fixture before running a snippet. The local verifier compares exact ordered row arrays in SQLite and PostgreSQL, keeps SQL values separate from driver bindings and never connects to the production website database.