Navigation auf uzh.ch

Suche

Department of Informatics Database Technology

Databases with Ongoing Time Intervals

Goal. The goal of this project is to evaluate queries on databases with ongoing time intervals to results that remain valid as time passes by. This allows using the query results as materialized views and it enables evaluating queries that cannot be correctly answered with state-of-the-art approaches.

Data that are associated with time intervals that denote the validity period of the data are present in many real-world applications. Examples include employment contracts, insurance policies, and telecommunication contracts. A contract often has a fixed start date and an end date that keeps increasing as time advances until the contract is modified or terminated. We say that the end date is ongoing. A typical example of an ongoing end date is now. Now as the end date denotes that the contract is valid until the present time. Since the present time continuously changes, the validity period of the contract does so as well.

Example. Consider the two types of employment contracts: fixed-term contracts and open-ended contracts. In a university, doctoral students have fixed-term contracts, i.e., contracts with a fixed start and a fixed end date, while professors have open-ended contracts, i.e., the start date is fixed and the end date is ongoing. An excerpt of employment contracts is given in relation E, which consists of the three attributes the name N of the employee, the employee's salary S, and the time interval VT of the employment.

Relation with ongoing time intervals at different present times.

John has a fixed-term contract and is employed with a salary of 60k from 2017/04 until 2020/01. Jane has an open-ended contract (indicated by the ongoing end point now in VT) and will be employed with a salary of 80k from 2017/09 onwards. The valid time of Jane's employment depends on the present time. At present time t = 2017/10, Jane is employed from 2017/09 until 2017/10 and at time t = 2017/11, she is employed from 2017/09 until 2017/11.

The state-of-the-art approaches evaluate queries on relations with ongoing time intervals to results that are only valid at the time the query was issued. Thus, these query results get immediately outdated as time passes by. As a consequence, using these results within materialized views requires re-evaluating the query whenever time advances to keep the view valid. Queries like the query “Determine for each project the employees who can work at the project from the project's start date onwards.” cannot be correctly answered.

Example. Consider the query “Determine the employees who can work at a project with period PT = [2017/10, 2019/10) from the project's start date onwards.” applied to employment relation E. The query evaluated at the three present times t = 2017/09, t = 2017/10, and t = 2017/11 is shown in the following.

Query results on relations with ongoing time intervals.

At present times 2017/09 and 2017/10 it is not observable that employee Ann can work at the project from its start date onwards. At present time 2017/11, it is observable that Ann can, indeed, work on the project. The reason why employee Ann is observable in the result at some present times and not at others is that the predicate PT.start ∈ VT evaluates to true only at some present times.

To evaluate queries to results that remain valid when time passes by, we provide solutions for the three key challenges

  • the computation of query results without instantiating ongoing time points,
  • the representation of the query results that remain valid, and
  • the intuitive interpretation of these query results.

PostgreSQL for Ongoing Databases

The source code is available for download, it contains compilation and installation instructions as well as sample relations and queries:

PostgreSQLOngoing (GZ, 21 MB)

Publications