Subject: SQL temporal language extensions

From: "Rick Snodgrass" <rts>
Date: Mon, 5 Jul 1993 15:32:21 MST

One major objective of the temporal database infrastructure workshop
(held three weeks ago in Arlington, TX) was to initiate the design of
a consensus temporal extension of SQL.  There were three conflicting
viewpoints on such an extension voiced by the workshop participants
(with associated rationales, not repeated here):

(a) With the addition of an interval data type, there will be
sufficient support in SQL2/3's data model to support applications
using temporal data.  Further temporal support within the data
model should not be added.

(b) SQL2, and the proposed SQL3, require temporal support to be added
to their data model and query language. A two-pronged effort should be
initiated, the first being a short-term effort to define a temporal
extension to SQL2 and the second being a long-term effort to define a
comprehensive extension to SQL3.

(c) Temporal support should be added, but only SQL3 should be
extended.

These three viewpoints are clearly at variance. So, instead of a
single consensual effort, which appears to be unattainable, three
separate efforts have been initiated, each reflecting the approach
espoused by a significant portion of the TDB community, and together
enabling further progress. The following working groups have been
started. If you would like to participate in one of these working
groups, please contact the group coordinator.

* The SQL2/3 working group
	Those agreeing with viewpoint (a) will define an interval data
type and write SQL2 (or SQL3) queries for the benchmark queries, so
that this approach can be compared with other proposals. Nikos
Lorentzos <[email-masked]> has volunteered to coordinate
this effort.

* The TSQL2 working group
	Those agreeing with viewpoint (b) will define a short-term
temporal extension to SQL2. I have volunteered to coordinate this
effort.

* The TSQL3 working group
	Those agreeing with viewpoint (b) or with viewpoint (c) will
define a long-term temporal extension to SQL3. Gene Wuu
<[email-masked]> has volunteered to coordinate this effort.

The initial reports of these working groups, if they are completed by
August 23, will be attached to final report of the workshop.  However,
the activities of these working groups are also relevant apart from
the workshop, and so should not be prescribed by it.  The workshop can
be viewed as an impetus for further consensual infrastructure
activities withing the temporal database community.

The specific charters of the working groups will be determined by the
coordinators. I now turn to discussing the particulars of the TSQL2
working group.

The TSQL2 working group design will proceed in the context of the
following constraints.

* The initial design must be completed by August 23, 1993.

* The design will be based on SQL2. In particular, none of the
	significant extensions envisioned in SQL3 will be assumed
	to be present.

* Initially, the language design will support user-defined time and
	valid time. Support for transaction time may be added later.

* The design will be implementable in the context of conventional
	relational database technology. In particular, implementations
	of the data model and query language constructs will be
	possible without necessitating major changes to conventional
	database management systems.

* Discrete time will be assumed.

* Past and future valid time will be supported.

The activities of this working group will be parallel to those of the
SQL2/3 and TSQL3 working groups. Interaction in the medium term
between these groups will attempt to arrive at consistent approaches.

---

From: I. Ahn <[email-masked]>
Date: Tue, 6 Jul 93 17:05:48 EDT

Greetings.
Please ignore it if you received this earlier.

I have some comments about the following constraints.

	> 	valid time. Support for transaction time may be added later.
							====================
The need for supporting transaction time is important, as numerous people
have commented in the workshop.
I believe support of transaction time will be a major contribution of the
"TSQL2 working group" in contrast with the approach of the
"SQL2/3 working group", and will serve as the basis for the
"TSQL3 working group" too.

I hope we can include this support from the beginning.
Since the semantics of transaction time is relatively simple, it may not
be difficult to agree upon a standard.

At least one commercial DBMS already supports the concept of transaction
time to some degree.
Sybase has the "timestamp" data type which is automatically updated
whenever a row containing a timestamp column is inserted or updated.
A restriction is that a table may have only one timestamp column.

What can happen is that other DBMS vendors may try to match or exceed
this feature in differing ways, and then the standard has to catch up
with the practices later on as it did for the user-defined time.

	> * Discrete time will be assumed.

I hope we can discuss this further.
To assume discrete time, the standard has to specify the granularity of time.
Some of the problems with this approach are:
  - Time is inherently continuous, as the real number is.
	Enough said about this in the workshop.
  - Granularity is an issue of representation.
  - It is not consistent with the "time" data type already defined
	for the user-defined time in the SQL2 standard.
    The standard says that the granularity is determined by each
	implementation, not by the standard, except that at least
	6 fractional digits should be supported.
    I am not sure we want to invent a new representation of time
	in addition to the above.

    In fact, SQL2 goes further and follows the convention of
    the [closed, open) time.
    e.g. the predicate
		(time '10:00:00', time '12:00:00) overlaps
		(time '11:00:00', time '13:00:00)	is true
	but
		(time '10:00:00', time '11:00:00) overlaps
		(time '11:00:00', time '13:00:00)	is false.
		
    I believe this is a good convention, and we need good reasons and
	a lot of efforts to change this.

Besides, we need to address not only the query statement, but also
the data definition and data manipulation statements such as
"create", "insert", "delete", and "update".
The query statement alone will not be very useful, if there is no
support for the other statements.
I think any proposal should be evaluated in terms of how it deals
or can deal with all these requirements as a whole.

Regards,
Ilsoo Ahn ([email-masked])