Joe Celko's SQL for Smarties, 3rd Edition

29.8. Modeling Time in Tables

Since the nature of time is a continuum, and the ISO model is half-open intervals, the best approach is to have (start_time, end_time) pairs for each event in a history. This is a state transition model of data, where the facts represented by the columns in that row were true for the time period given. For this to work, we need the constraint that the (start_time, end_time) pairs do not overlap.

A NULL ending time is the flag for an “unfinished fact,” such as a hotel room stay that is still in progress. A history for an entity can clearly have at most one NULL at a time.

CREATE TABLE FoobarHistory (foo_key INTEGER NOT NULL, start_date DATE DEFAULT CURRENT_DATE NOT NULL, PRIMARY KEY (foo_key, start_date), end_date ...