2026-09-08 · 8 min read
- sql
- timezones
- data-modeling
Temporal bugs begin when one value is expected to represent several concepts. An instant identifies a point on the timeline, a local date-time describes wall-clock fields without a unique offset, and a calendar date has no time or timezone.
Database type names and conversion behavior differ across engines. A schema therefore needs a semantic contract in addition to a column declaration.
Model the domain concept first
Use an instant for events that already happened or deadlines shared globally. Use a local date-time plus an IANA timezone identifier for recurring regional schedules, and use a date type for birthdays or billing dates when no clock time is intended.
- Do not use a fixed offset as a substitute for a regional zone.
- Keep date-only values out of instant conversion paths.
- Document accepted API offset and fractional precision.
- Name integer epoch fields with their explicit unit.
Control database sessions and drivers
Set connection timezone behavior explicitly and verify it after pool checkout when the engine allows session mutation. Bind typed parameters rather than assembling temporal literals whose interpretation depends on server locale or session state.
Inventory how the driver maps columns into runtime objects and how serializers handle those objects. A driver upgrade can change parsing defaults without changing SQL or schema.
Define daylight-saving and precision policy
Regional offset changes create local times that occur zero or two times. A scheduling contract must reject, shift, or choose an occurrence explicitly and preserve the timezone identifier for future recalculation.
- Test both sides of gaps and overlaps.
- Do not infer causality from wall-clock ordering.
- Align database, runtime, and API fractional precision.
- Use a stable tie-breaker when timestamps can be equal.
Verify every round-trip boundary
Use Flashman's SQL formatter for statements, timestamp converter for instant and offset fixtures, JSON formatter for wire representations, diff for round trips, and units converter for epoch unit checks.
Test UTC, positive and negative non-hour offsets, daylight-saving transitions, connection reuse, replicas, background jobs, date-only values, maximum fractions, range limits, exports, driver upgrades, and every supported database engine.