How Should a Dataset Represent a Time Interval?
From the earlier reference collection. Read the current software blog.
An interval needs clear endpoint rules, a time basis, and a consistent representation so users can determine exactly which moments it covers.
Define the boundaries
A pair of timestamps does not tell a reader whether the endpoints are included. An interval from 10:00 to 11:00 could include both boundary instants, exclude both, or include only one. State the convention with the field definition and use it consistently across queries, exports, and examples.
A common useful convention is a half-open interval: include the start and exclude the end. Then adjacent intervals from 10:00 to 11:00 and 11:00 to 12:00 meet without counting 11:00 twice. Whatever convention a dataset chooses, a reader should not have to infer it from sample data.
Identify the time zone
A timestamp can refer to UTC, a named local time zone, or a local clock reading with no zone. Those forms are not interchangeable. If records represent global instants, use a representation that identifies the offset or explicitly define the stored basis as UTC. If the interval follows a local calendar or operating schedule, name the relevant zone and explain how boundaries are formed.
Daylight saving transitions make local intervals especially important. A local day may not have the same elapsed duration as an ordinary day. For example, a local midnight-to-midnight interval should be interpreted using the named zone's rules, not by assuming every day contains a fixed number of seconds.
Choose a representation that preserves intent
Store start and end separately when users need to filter or compare intervals. A single human-readable string can be useful for display, but it is harder to validate and process. Include an explicit zone where needed, and distinguish an instant from a date-only boundary such as “the reporting day is Monday.”
For recurring intervals, specify whether the values represent local wall-clock times, elapsed durations, or exact timestamps. A rule such as “open from 09:00 to 17:00 local time” repeats by local clock; it is not equivalent to adding eight hours to an arbitrary UTC instant on every date.
Validate edge cases
Test equal endpoints, intervals crossing midnight, adjacent intervals, and daylight saving changes if local time is involved. Decide whether zero-length intervals are valid and how an open-ended interval is represented. Do not use a magic date or empty string for an unbounded end without defining it.
A complete interval contract answers four questions: what the endpoints mean, what time zone applies, which representation is used, and how exceptional cases are encoded. Those answers let a reader reproduce filters and compare records without accidentally including an extra instant or shifting a boundary.