Chronus FAQ
What is Chronus?Chronus is a database mediator that extends the standard relational model and the SQL query language to support temporal queries on relational databases. It provides an expressive SQL-based temporal query langauge that allows users to easily write complex temporal queries.Why would I need a temporal query system?Questions relating to time are pervasive when performing queries on relational databases. Common queries include: "For how long did a particular event last?", "Did the event occur after another event?", "When was the most recent occurrence?", and "Were there at least three occurrences of a particular event lasting over a week?"Can't I just use SQL to make these queries?Relational database systems provide very poor support for writing temporal queries. The SQL query language has a very limited set of temporal operators and the relation model is mostly agnostic about the temporal content of the data that it stores. Hence, writing temporal queries in SQL can be very difficult. Writing a temporal query using SQL typically require formulating multiple SQL queries, with the creation of several intermediate temporary tables. Some operations, such as coalescing, which can theoretically be expressed in SQL, are impractical.The most significant shortcomings of the relational model and SQL are:
The fundamental problem is that the relational model is two-dimensional: rows and columns are the axes of two-dimensional tables. Time adds a third dimension to the model, and simply adding extra time columns to a two-dimensional temporal table is not sufficient to turn it into a three-dimensional temporal table. The standard relational model treats all columns equally and would not capture the semantics of the extra temporal columns. Applications that work with complex temporal data must thus define their own temporal models and query systems. The query systems often require custom code and reside outside the database. Because of the relational model's limited temporal support, thousands of applications per year must reinvent the wheel and develop their own temporal query systems. Is the Chronus query language compatible with standard SQL?In principle, yes. The Chronus temporal query language is a strict superset of the SQL89 grammar. Thus, it accepts all standard SQL89 queries and works without modification on standard relational databases. The Chronus query language does not fully support many SQL92 features, but neither does any DBMS that we are aware of. The grammar used as the basis of the Chronus parser is based on the variabnt of SQL supported by Oracle 7. The BNF for this grammar can be found here.What Temporal Model does Chronus Use?Chronus uses the standard valid-time temporal model, which is the most widely used model employed in temporal-relational database systems.Where can I read a description of the Temporal Model used by Chronus?This model is discuessed extensively in temporal database research papers but the best standalone description is contained in Richard Snodgrass' The TSQL2 Temporal Query Language.The core concepts of this model that are used by Chronus are described here. Is the Chronus temporal model compatible with the standard relational model?Yes. The temporal schema is a strict superset of the standard relational schema. Chronus supports all existing non-temporal database schemas. Thus, for example, Chronus could be used as a query processor interface to existing non-temporal DBMSs.Are there any papers describing Chronus?A list of Chronus related publications can be found here.Where can I get Chronus?Chronus will soon be available for download at:http://chronus.stanford.edu/downloads/ Chronus is written in Java and is distributed as as thow JAR files. Java applications that wish to use Chronus can simply put these JARs in their CLASSPATH. The current version uses the 1.4.2 release of Java, though it should work with 1.3 or later versions also. This JAR has no dependiencies beyond the Java VM and by default the standard Sun JDBC driver (which is included in the standard distribution) is used to connect to a database. However, if other JDBC drivers are used, they will also need to be in the class path (see here). How does Chronus interact with a database?Chronus is designed to operate as a client of existing relational databases. It interacts with these databases through a JDBC interface.What DBMSs does Chronus support?Chronus has been tested with SQL Server 6 and above, Oracle 8, and mySql XXX and above. In principle, it should work with any SQL89 compliant DBMS using JDBC. However, because of implementation idiosyncracies of indivudual databases, it may not work out of the box.How do I use Chronus?The are two primary ways of interacting with Chronus: (1) using Java APIs, (2) using command line Chronus tools. The Chronus APIs are described here. The command line tools are outlined here.How do I run Chronus from the command line?Users may use Chronus interactively or in batch mode.To run Chronus interactively, type: java edu.stanford.smi.ChronusII.clients.ChronusIIInteractive <initialization_file> The format of the initialization file is described here. This file identifies the database to connect to. In interactive mode, users will be given a command line interface to the database that they are connected to. All standard SQL or Chronus commands can be used in this mode. Any results from queries are displayed interactively. To exit this mode, the user can type QUIT or EXIT. Users can also display a list of the temporal tables in the database using the LIST command. Case is not significant for commands or queries. Chronus batch mode invocation is similar except that an extra parameter is given containing the name of a file containing the list of SQL or Chronus commands that are to be executed: java edu.stanford.smi.ChronusII.clients.ChronusIIBatch <initialization_file> <batch_file> The format of the initialization file is described here.
The first table, PROBLEMLIST, contains diagnoses for a set of patients; the second table, DRUGS, contains drug regimen information for those patients. The ValidTime column shows the time periods during which the associated tuple is valid. The '+' value denotes that the tuple is still valid. The developer is then asked to write the following query: Show problem and drug combinations for patients whose problem was diagnosed in the last year. Restrict the results to combinations involving drug regimens lasting longer than two weeks and excluding combinations where a drug regimen was initiated before the onset of a problem. This query can be written as a temporal join in Chronus as follows:
TEMPORAL SELECT P.Patient, P.Problem, D.Drug
FROM PROBLEMLIST AS P, DRUGS AS D(Patient, Drug)
WHERE P.Patient = D.Patient
WHEN DURATION(D, 'weeks') > 2 AND
DURATION(START(P), 'now', 'years') < 1 AND
BEFORE(START(D), START(P))
The following temporal table is the result of this query:
This query resembles standard SQL with some extra clauses. Apart from the initial TEMPORAL keyword, the most obvious addition is the WHEN clause. This clause is analogous to the WHERE clause and contains temporal predicates. In this case, the predicates are two forms of the temporal operator DURATION and the BEFORE operator. The first form of the DURATION operator takes a table and a granularity as parameters and calculates interval lengths in the table at the specified granularity. The second form takes two timestamps in addition to a granularity specification. The first timestamp is extracted from the start of the valid-time of the PROBLEMLIST table using the BEGINNING function, and the second uses the special 'now' value, which represents the time that the query is executed. The FROM clause also contains a unique temporal extension called a temporal projection. The DRUGS table shows a history of drug and dosage information for a set of patients. In the query, however, the dosage is not relevant. Hence, the query forms a projection that excludes the dosage information. Thus, for example, the two adjacent intervals of the atenolol prescriptions at differing doses for J. Smith are projected into table P and coalesced into a single interval. This six-line query demonstrates a temporal projection, a temporal join, coalescing, granularity conversion, and several temporal operators. An equivalent SQL query would be far less concise or readable, would probably need to be written as multiple queries, and would require temporary tables to hold intermediate results. Is there a BNF for the Chronus query langauge?A BNF for the Chronus query language can be found here. This BNF is based the BNF for Oracle V7's SQL. All temporal commands can be distinguished from standard relational commands by the initial TEMPORAL keyword. These commands are grouped under the temporal_command node in the BNF. All standard relational commands work normally.What basic types does Chronus add to support temporal queries?Chronus defines the following basic data types: (1) calendars, which specifies a set of granularities and the relationships between them; (2) granularities, which specify the resolution of a timestamp with a calendar; (3) datetimes, which are used to specify individual points in a timeline; (4) instants, which represent arbitrary points in a timeline; (5) periods, which represent segments of a timeline; and (6, 7) state and event tables, which extend standard relational tables with temporal information.All of theses types are described in the remainder of this FAQ. What calendar does Chronus support?Chronus by default uses the standard Gregorian calendar, which can be identified in a calendar clause by enclosing the term 'Gregorian' in single quotes. Case is not significanr. At present, no other calendars are supported but the syntax is designed to be extensible so that other calendars can be added in future without effecting backward compatability. Calendars are always used only in association with granularities in Chronus.ExamplesGregorian GREGORIAN What granularities does Chronus support?A timestamp's granularity defines the resolution at which it is recorded. For example, some timestamps are recorded with a resolution of days; others are specified with a resolution of minutes. Every calendar will have a set of granularities defined for it. The granularities supported by the Gregorian calendar in Chronus are:
All granularity specifications in the Chronus query langauge must be enclosed in single quotes. They are not case sensitive. A granularity can optionally be preceded by a calendar specification, though at present only the Gregorian calendar is supported. Chronus supports automatic granularity conversion in queries so, for example, if a piece of information is stored with a granularity of days and a query specifies minutes, it will convert days to minutes for query evaluation. Examples'Gregorian.days' 'WEEKS' 'Years' How are datetimes represented by Chronus?A datetime is an isolated point in time known to have a given granularity. Chronus used the standard JDBC datatime format of YYYY-DD-MM HH:MM:ss:ms.All datetime literals must be enclosed in single quotes. A datetime does not have to be specified to the finest granularity - partial specifications are allowed. So, for example, only a year and month may be specified. Chronus will round partial specifications the start of the each unspecified granularity. So, '1999-02' will be rounded to '1999-02-01 00:00:00.000'. Chronus supports three special datetime values. A special value of 'now' is used to represent the time when a temporal command is executed. For example, if a command wishes to restrict itself to tuples that are currently valid, it can use the 'now' value to represent the current time of the query. A related distinguished value is '+', which can be used to represent the end time of tuples that are valid until otherwise specified. Such ongoing tuples are common in databases and the use of the + value allows one to specify that a tuple's validity is ongoing without resorting to using artificial future datetime constants. The '-' distinguished datetime value can be used to specify that a tuple is valid arbitrarily far into the past. It effectively specifies tuples that represent facts that have been true since the beginning of time (or at least since the beginning of the current calendar). There is no analogous end-of-time distinguished datetime - to state that a tuple will remain valid to the end of time is a strong statement, and one that Chronus does not support. It is possible, however, to specify datetime values that are beyond the current time. Examples'1999' 'now' '2001-12-1' '2000-2-1 12:33:12.333' '2005-3-1 11:33' '+' '-' How are instants represented by Chronus?An instant is a point on the time-line. In Chronus an instant is specified using a datetime and a granularity.An instant may be represented in Chronus by using the INSTANT clause. A granularity may optionally be specified and the datetime is rounded to that granularity when it is stored. Examples
INSTANT('now')
INSTANT('-')
INSTANT('+') -- INVALID: ongoing datetime can not be used as an instant
INSTANT('1998', 'days') -- converted to '1998-1-1'
INSTANT('1997-12-2', 'months') -- converted to '1997-12'
How are intervals represented by Chronus?An interval may be represented in Chronus by using the PERIOD clause, with an optional granularity clause. If a granularity is specified the datetime supplied to converted to that granularity. If the granularity is finer than the supplied start datetime's finest granularity, it is converted to the last possible chronon at the supplied granularity; similarly, the granularity is finer than the supplied finish datetime's finest granularity, it is converted to the first possible chronon at the supplied granularity.Examples
PERIOD('1998-12-1', 'now')
PERIOD('1998', '2000', 'milliseconds) -- converted to '1998-12-31 23:59:59.999', '2000-1-1 00:00:00.0'
PERIOD('1998', '1997') -- INVALID: start time must precede stop time
PERIOD('2008', 'now') -- POTENTIALLY INVALID: start time may be after stop time at evaluation time
PERIOD('+', '1999') -- INVALID: ongoing instants cannot be used as a start time
PERIOD('1999', '-') -- INVALID: the beginning of time value cannot be used as a finish time
PERIOD('-', '+')
How do I create a temporal table in Chronus?There are two types of temporal tables supported by Chronus: event tables and state tables. Event tables hold instant timestamps, and state tables hold interval timestamps. For example, laboratory values are typically stored in event tables, while information about drug regimens can be held in state tables, because drug treatments generally extend over time.A temporal table is created in a similar way to a normal relational table. The TEMPORAL keyword is used to indicate that the command is a temporal create and the column list is followed by a valid clause specifying if the table is a state or event table. An optional granularity may also be provided. ExamplesTEMPORAL CREATE TABLE Treatment(ID INTEGER, Regimen VARCHAR(100)) AS VALID STATE 'days'; TEMPORAL CREATE TABLE Sequen(ID INTEGER, SeqName VARCHAR(50)) AS VALID EVENT 'days'; TEMPORAL CREATE TABLE RNA(ID INTEGER, VLoad FLOAT) AS VALID EVENT; How do I insert data into a temporal table in Chronus?Temporal insertions are to normal relational insertions with the difference that the valid time of each row inserted must be specified. The TEMPORAL keyword is used to indicate that the command is a temporal insert and the value list is preceded by a valid clause specifying the one or more instants or periods that are associated with the row.An exception will be thrown is an attempt is made to insert an interval into an event table. An instant can be inserted into a state table; if it is, it is converted to a period with the same start and stop time. Examples
TEMPORAL INSERT INTO Treatment
VALID PERIOD('1998-01-01', '1998-02-01') +
PERIOD('1998-02-03', '1998-03-01')
VALUES(1, 'ABC');
TEMPORAL INSERT INTO Treatment
VALID PERIOD('1999-01-01', '1999-02-01')
VALUES(1, 'DDI');
TEMPORAL INSERT INTO Sequen
VALID INSTANT('1999-02-03')
VALUES(1, 'BRU');
TEMPORAL INSERT INTO RNA
VALID INSTANT('1999-02-03') + INSTANT('1999-02-08') + INSTANT('1999-02-12') + INSTANT('1999-0-20')
VALUES(1, 2.0);
TEMPORAL INSERT INTO RNA
VALID INSTANT('1999-02-06') + INSTANT('1999-04-07') + INSTANT('1999-04-06')
VALUES(1, 2.1);
TEMPORAL INSERT INTO RNA
VALID INSTANT('1999-02-07') + INSTANT('1999-03-06') + INSTANT('1999-03-06')
VALUES(1, 2.6);
TEMPORAL INSERT INTO RNA
VALID INSTANT('1999-02-11')
VALUES(1, 2.8);
How do I drop temporal tables in Chronus?ExamplesTEMPORAL DROP TABLE Treatment; TEMPORAL DROP TABLE Sequen; TEMPORAL DROP TABLE RNA; What are the temporal operators supported by Chronus?Chronus supports the following temporal operators:
Examples
TEMPORAL SELECT T.ID, S.name
FROM Treatment AS T, Sequence AS S
WHERE T.ID = S.ID
WHEN OVERLAPS(S, T)
TEMPORAL SELECT T.ID, S.name
FROM Treatment AS T, Sequence AS S
WHERE T.ID = S.ID
WHEN OVERLAPS(S, PERIOD(FINISH(T), 'now'))
TEMPORAL SELECT T.ID, S.name
FROM Treatment AS T, Sequence AS S
WHERE T.ID = S.ID
WHEN OVERLAPS(S, PERIOD('1999', 'now')) AND MEETS(S, T)
TEMPORAL SELECT T.ID, S.name
FROM Treatment AS T, Sequence AS S
WHERE T.ID = S.ID
WHEN DURING(T, PERIOD('1999', '2000))
TEMPORAL SELECT T.ID, S.name
FROM Treatment AS T, Sequence AS S
WHERE T.ID = S.ID
WHEN DURATION(T, 'months') > 2 AND OVERLAPS(S, PERIOD(START(T), 'now'))
Does Chronus support orginal selection?Yes, Chronus supports ordinal selection using the following four keywords:
ExamplesTEMPORAL SELECT FIRST T.ID, S.name FROM Treatment AS T, Sequence AS S WHERE T.ID = S.ID WHEN DURATION(T, 'months') > 2 TEMPORAL SELECT NTH(3) T.ID, S.name FROM Treatment AS T, Sequence AS S WHERE T.ID = S.ID WHEN DURATION(T, 'months') > 2 TEMPORAL SELECT NTH(3, 4) T.ID, S.name FROM Treatment AS T, Sequence AS S WHERE T.ID = S.ID WHEN DURATION(T, 'months') > 2 Does Chronus support temporal projections?Yes. Many temporal queries will wish to project selected columns in a table before operating on them. The resulting projection may be coalesced or uncoalesced. Given that many temporal queries will operate in this way, we would like to avoid requiring that the projection be performed in a separate SELECT statement because this approach would necessitate the writing of two SELECT statements for many temporal queries. Instead, we allow projection to be performed in the FROM clause of a SELECT statement. These projections are named so that we can refer to them in the subsequent WHERE clause. They are coalesced by default; the keyword UNCOALESCED may be used to turn off coalescing.Suppose we had the following table called EMPLOYEE and wanted to select people who had worked for more than two months at any job.
We could write the query as follows using a coalesced temporal projection in the FROM clause: TEMPORAL SELECT Name FROM Fred(Name) AS P WHEN DURATION(P, 'Months') > 2 Does Chronus support temporal joins?Yes - Chronus has extensive support for temporal joins. Relationally joining multiple tables involves generating a subset of the Cartesian product of all tuples in the tables. In a SQL query, this subset is determined by the columns specified in the SELECT clause and by the predicates in the WHERE clause. In a temporal join, it is further restricted by temporal predicates. However, a temporal join differs from a non-temporal one because the temporal attributes in the operand tables must be treated specially. To perform temporally valid joins, we must combine the temporal information from the operand tables and generate a single meaningful temporal column.The valid-time component in each temporal table must be well-defined before performing such joins. If all temporal information and queries concerned past events, capturing the semantics of the information would not be difficult. However, temporal databases must also describe events that are ongoing, and may also need to describe future events. Thus, the range of temporal information modeled by a table's valid-time component must be carefully defined before it can be used in a join. This definition will also prove crucial when joining temporal with non-temporal tables. Chronus supports three principal types of temporal joins: (1) state table with state table, (2) event table with event table, and (3) state table with event table. To simplify the discussion, only two-way joins are shown. Joins of three or more tables can be implemented using a succession of two-way joins. In order to perform a two-way join, we must first assemble the non-temporal columns as we would for a non-temporal join. The columns are assembled by generating the cross product of the non-temporal columns from the operand tables, and then excluding rows that do not satisfy the predicates in the WHERE and WHEN clauses. The temporal columns must be treated separately. We must consider the semantics of the valid-time component in each operand tuple before attempting to combine these tuples into the final joined table. If we wish to combine elements from multiple tuples in a temporal join, we can do so only if the facts they capture are true at the same time. That is, the temporal periods associated with each tuple must overlap. Thus, we must examine the source tuples for each candidate tuple in the reduced cross product to see if their valid-time periods intersect. If they do overlap, the candidate tuple is included in the final join product and the result of this intersection is used as the valid-time period of the new tuple. If they do not overlap, this tuple is excluded from the result of the join. Thus, the join preserves the original temporal semantics. A similar approach works when joining event tables. Because we are now dealing with instant time stamps, we can determine if the tuples can be combined temporally by using the temporal equality operator. That is, tuples from two event tables can be joined into one table only if their instant time stamps are equal. If they are, the events clearly occurred at the same time, so their combination is temporally valid. The instant time stamp for each tuple in the product table can be taken directly from any one of its operand tuples. In order to combine rows in a temporal join, a row contained in an event table must occur during the lifetime of a row contained in a state table. Therefore, to combine an event tuple with a state tuple, the event tuple's time stamp must occur during the period specified by the state tuple's time stamp. The product tuple's valid-time will be the instant that they overlapped. As a consequence, joining a state table and an event table will always produce an event table. Chronus also supports joining temporal with non temporal tables. There are two main approaches for combining a temporal with a non-temporal table: (1) turn the temporal table into a non-temporal table, or (2) turn the non-temporal table into temporal table, and then join the two tables. A temporal table can be transformed into a non-temporal table by removing its valid-time component. However, stripping the time component will not produce a valid table because doing so effectively promotes all historical information into the present time. Excluding tuples that are not valid when the stripping takes place avoids this problem. In either case, all historical information is lost. If we wish to ask temporal questions, we must produce a temporal table from a non-temporal one. This process requires generating a suitable valid-time component for the non-temporal table. The non-temporal table can be converted into an event table with the present time as its valid-time component. However, this approach is unlikely to retain the semantics of the original table. For example, a non-temporal-turned-event table could inaccurately classify a patient's gender as true only at the present time. A state table provides a more flexible solution. An obvious approach is to construct a valid-time component with 'beginning', and 'forever' as its start and end time stamp, respectively. Other possibilities include using 'now' and 'until-changed' as the end time stamp. However, without knowing the meaning of the original non-temporal data, we can never hope to be fully accurate in our choice. In any case, using 'beginning' and 'forever' as start and end time stamps will generally produce satisfactory results. Once a suitable valid-time component for the non-temporal table is chosen, the join can proceed as normal. Performing joins on temporal tables is non trivial and any system that includes temporal information must rigidly define the semantics for such joins. For example, we cannot join two temporal tables using the semantics of the standard join because our resulting table would have two valid-time columns. Such a table will not fit our temporal model. Instead, we must define our own semantics for temporal joins. |