3.9 SUMMARY

Temporal values are the stuff of which time-varying applications are made. In order to record the history of the modeled reality, it is first necessary to be able to record the "when."

Instants are the most basic data type. An instant is a position on the time line. Virtually all DBMSs support this data type. In SQL-92, five related data types encode instants, to various granularities: DATE, TIME, TIMESTAMP, TIME WITH TIME ZONE, and TIMESTAMP WITH TIME ZONE.

Intervals are unanchored, directed portions of the time line; an interval can be added to an instant to displace that instant either into the future or back into the past. SQL-92 supports two kinds of intervals, year-month intervals and day-time intervals.

A rich set of operators and predicates applies to temporal values. SQL-92 provides the following classes of predicates: equality, inequality, is null, and overlaps. It also provides arithmetic operators (' +', ' -', ' *', ' /'), unary plus and minus, time zone conversion (AT), "now" (CURRENT_TIME, CURRENT_DATE, CURRENT_TIMESTAMP), a variety of conversions (CAST), and field extraction (EXTRACT).

Specific DBMSs vary greatly in their support of the standard, from quite strict adherence (e.g., IBM DB2) to studied apathy (most DBMSs). While most temporal operations in SQL-92 can be simulated in the facilities of the various DBMSs (and vice versa), that simulation is often unnecessarily convoluted.

Despite the care with which the SQL-92 standard has been developed and documented, it still contains dark corners and seemingly...