Temporal data are often characterized as data that vary with time, for example when we need to track all modifications of the attributes of an entity, or when we need to plan accommodations for hotel rooms in advance. I take a slightly broader view than that, encompassing also simple timestamped data, because I believe that the theory behind the former kind of data can also help in using the latter, and the two kinds of data interact anyway, in referential integrity constraints or when transforming from one to the other.
There are at least two dimensions of time that are usually employed in temporal databases, usually called valid time and transaction time. The valid time of some information marks when that information is supposed to hold in real life. In that sense, valid time in the past signifies the recorded past and valid time in the future signifies information that does not actually exist yet, but we know it is scheduled to. The transaction time of some information marks when this information was included in the database. As such, it records the history of modifications of a database. Blending the two dimensions, we have bitemporal databases that record the history of modifications of a database that records time-varying information. Pandamator currently supports only the valid time dimension.
Data varying with time will be described by rows annotated with a time period, represented in SQL by two datetime columns, namely ValidFromDate and ValidToDate. Together, they record a Closed-Open interval in the valid time dimension, meaning that the associated row is valid at any time between ValidFromDate inclusive and ValidToDate exclusive. Closed-open intervals can be chained back-to-back to cover longer periods. To be frank, using closed-closed intervals would allow a uniform representation that could be used for both aforementioned kinds of temporal data, but chaining closed-closed intervals would bring up the issue of the granularity of time in an RDBMS and how to form such intervals. Messy.
Data sets of time-varying data will be called state sets, as they record distinct states of the world at different instants of time.
… ValidFromDate DATETIME NOT NULL, ValidToDate DATETIME NOT NULL, CHECK (ValidFromDate < ValidToDate), …
Timestamped data will be described by rows annotated with a timestamp, represented in SQL by a datetime column named ValidOnDate.
Data sets of timestamped data will be called event sets, as they record distinct events in time.
… ValidOnDate DATETIME NOT NULL, …
I also make the distinction between explicit and implicit data. Explicit data are data actually stored in SQL tables containing the annotation columns just described. Implicit data, on the other hand, are data that are described indirectly but are, nevertheless, most useful to us when transcribed to state or event sets. Such data in Pandamator are periodically scheduled data and data described by state transition rules on event sets.
The last row of a history needs to have some specific value for its ValidToDate, which we might intend to be ‘now’, ‘forever’, ‘don’t know’, ‘until changed’ or NULL i.e. no value at all. In order not to digress in this discussion, the value I use is January 1st, 3000, which is sufficiently distant not to be mistaken for an actual input value and can be represented by virtually all RDBMSs. But practically any distant value will do, since the interval it forms with a real one will make sense.
A temporal database can be thought of as containing a multitude of non-temporal databases, one for each moment in time. Projecting a temporal database to the time dimension produces what is called a snapshot, which is the state of the described universe of discourse at the specific moment.
 |
Sequence of snapshots through time |
Producing a snapshot is very simple with the representation I have described, as it suffices to adorn every WHERE clause of every subquery with the condition p.ValidFromDate ≤ T and T < p.ValidToDate, where T is the moment in question, for every table p that appears in the subquery. The simplicity of this operation, sadly, is not shared with any other operation in the field of temporal support.
