Dates, ranges, and deterministic periods

The idea

SQLite provides date and time functions but does not have a dedicated mandatory timestamp storage type. A project should adopt a consistent representation and timezone policy. For sortable text timestamps, a consistent ISO-style format lets ordinary range comparisons follow chronological order. Mixing local times, offsets, and formats without normalization undermines that assumption.

A half-open interval includes its start and excludes the next period’s start. This avoids guessing the final representable instant of a day and works when fractional seconds are present. Define whether the report concerns UTC days or a named local calendar before calculating boundaries. Formatting a timestamp for display is different from choosing a filtering interval; retain the original value for comparisons when possible.

WITH events(id,ts) AS (VALUES(1,'2026-01-01 00:00:00'),(2,'2026-01-01 23:59:59.500'),(3,'2026-01-02 00:00:00'))
SELECT id FROM events WHERE ts>='2026-01-01 00:00:00' AND ts<'2026-01-02 00:00:00' ORDER BY id;

id
1
2

The start is included and the next midnight is excluded, while the late fractional-second event remains included.