Where does time go

1
Where does time go ?
2
Applications abound

• Temporal database systems provide built-in
  support for recording and querying time-varying
  information
• Application areas
• Media planning advertisements
• Accounting cash flow analysis
• Financial stock market
• GIS land use
• Medical patient records
• Academic course attendance
• Scientific archeological findings

3
Applications abound

• It is difficult to identify applications that do
  not need support for temporal data
• Temporal database systems provide built-in
  support for recording and querying time-varying
  information
• Temporal applications would benefit from built-in
  support at the data model and query language
  layers

4
What time ?
5
Time ontology

• Time is an ordered sequence of points
• Linear time
• Branching time (futures)
• Partial time ordering (DAG)
• Cyclic times
• Bounded or unbounded time lines
• Bounded origin exist and it ends
• Unbounded only partial order is known

6
Time ontology

• Time density
• Discrete
• Time line is isomorphic to the integers
• Time line is composed of a sequence of
  non-decomposable time periods of some fixed,
  minimal duration, termed chronons
• Between each pair of chronons is a finite number
  of other chronons
• Dense
• Time line is isomorphic to rational numbers
• Between each pair of chronons is a finite number
  of other chronons
• Continuous
• Time line is isomorphic with the real numbers
• Between each pair of chronons is a finite number
  of other chronons

7
Time ontology

• A time instant is a time point on the real time
  line
• An event is an instantaneous fact, i.e something
  occurring at an instant. The event occurrence
  time of an event is the instant at which the
  event occurs in the real world
• An instant set is a set of instants
• A time period is the time between two instants
• also called interval
• A time interval is a directed duration of time. A
  duration is an amount of time with a known
  length, but no specific starting or endings
  instants. It is also called a span.
• A temporal element is a finite union of periods

8
Clocks

• A clock is a physical process coupled with a
  method of measuring that process
• The units of measurement are the chronons of the
  clock
• Clocks provide the semantics of the timestamp
  representation
• Clocks can be defined over various calendar
  structures

9
Times and facts

• Valid time of a fact when the fact is true in
  the modeled reality
• Transaction time of a fact when the fact is
  current in the database and may be retrieved
• Four kinds of temporal tables
• Snapshot table without time
• Valid-time
• Transaction-time append only tables
• Bi-temporal

10
Transaction and valid time tables

• Transaction time tables
• Append only corrections to previous snapshot
  states is not permitted
• Support of transaction time
• Support rollback queries
• Valid time tables
• May be modified
• Support valid time
• Short historical queries

11
Cant we use SQL for temporal support ?
12
Time Specification in SQL-92

• date four digits for the year (1--9999), two
  digits for the month (1--12), and two digits for
  the date (1--31).
• time two digits for the hour, two digits for the
  minute, and two digits for the second, plus
  optional fractional digits.
• timestamp the fields of date and time, with six
  fractional digits for the seconds field.
• Times are specified in the Universal Coordinated
  Time, abbreviated UTC (from the French) supports
  time with time zone.
• interval refers to a period of time (e.g., 2
  days and 5 hours), without specifying a
  particular time when this period starts could
  more accurately be termed a span.

13
A case study

• Consider a simple university database
• Employee(Name,Salary,Title)
• Finding an employees salary is easy
• Select Salary
• From Employee
• Where name Bob

14
A case study

• Now consider addition of a Date-of-birth field
• Employee(Name,Salary,Title, Dob DATE)
• Finding the employees date of birth is easy
• Select Dob
• From Employee
• Where NameBob

15
A case study

• Extend the database with an employee history
• Employee(Name,Salary,Title, Dob,
• Start DATE, stop DATE)
• To the datamodel these columns are identical to
  Dob

16
A case study

• Find the current salary
• Select Salary
• From Employee
• Where NameBoband Start lt current_time_stamp
• And current_time_stap lt Stop

17
A case study

• Find the salary history
• Select Salary, start, stop
• From Employee
• Where NameBoband Start lt current_time_stamp

Coalesce these tuples
18
A case study

• Find the salary history but take into account the
  title
• Give the user a print out
• Or combine intervals with the same function and
  salary, I.e. compress the table as much as
  possible

19
A case study

• Find the salary history but take into account the
  title
• Or combine intervals with the same function and
  salary, I.e. compress the table as much as
  possible
• Create table Temp(salary,start,stop) as select
  Salary,Start,Stop from Employee where Name
  Bob
• Repeat
• Update Temp as T1
• Set t1.stop (select max(T2.stop) from Temp as T2
  where T1.salaryT2.salary and T1.startltT2.start
  and T1.StopgtT2.Start and T1.stopltT2.stop)
• Where exist( select from Temp as T2 where
  T1.salaryT2.salary and T1.Start ltT2.start and
  T1.startltT2.start and T1.StopgtT2.Start and
  T1.stopltT2.stop)
• Until no tuples are updated

20
A case study

• Find the salary history but take into account the
  title
• Or combine intervals with the same function and
  salary, I.e. compress the table as much as
  possible
• Use standard SQL leads to triple nested queries
• Use SQL cursor methods and rely on iterations

21
Objectives of a temporal model

• Conclusion It would be nice if the datamodel and
  query language understood the intended temporal
  behavior
• Capture the semantics of time-varying information
• Retain simplicity of the relational model
• Strict super-set of relational model
• Present information in a coherent fashion
• Ensure ease of implementation
• Ensure high performance
• Design your own extension to the relational model

22
Temporal Relational Model and algebra

• What kind of temporal data would you introduce in
  the relational model ?
• Where would you introduce temporal components?
• How would you define the interpretation of the
  relational primitives, I.e.
• Temporal selection
• Temporal projection
• Temporal join
• Temporal union
• How would your extend the integrity rules ?

23
Temporal Relational Model and algebra

• What kind of temporal data would you introduce in
  the relational model ?
• Calender information
• Timezones
• Accuracy
• Time administration
• Points on a time axe
• Intervals
• Sets of points
• Sets of intervals
• Fake time

24
Temporal Relational Model and algebra

• Where would you introduce temporal components?
• AT the schema level
• At the table
• At the column level
• At the attribute level
• At the tuple level

Temporal tuple
25
Temporal Relational Model and algebra

• What kind of integrity rules ?
• The key property is extended to include the
  temporal semantics
• No two tuples defined at the same time have
  identical values
• Referential constraints should obey the temporal
  relationships as well

26
Temporal Relational Model and algebra

• How would you define the interpretation of the
  relational primitives?
• When time is a tuple attribute, then the tuples
  are coalesced on their temporal attribute
• Duplicate elimination respects the temporal
  dimension
• Relational joins are only defined for the same
  time intervals, outerjoins should be used
  otherwise.

27
Time In Databases

• While most databases tend to model reality at a
  point in time (at the current'' time), temporal
  databases model the states of the real world
  across time.
• Facts in temporal relations have associated times
  when they are valid, which can be represented as
  a union of intervals.
• The transaction time for a fact is the time
  interval during which the fact is current within
  the database system.
• In a temporal relation, each tuple has an
  associated time when it is true the time may be
  either valid time or transaction time.
• A bi-temporal relation stores both valid and
  transaction time.

28
Time In Databases (Cont.)

• Example of a temporal relation
• Temporal query languages have been proposed to
  simplify modeling of time as well as time
  related queries.

29
Temporal Query Languages

• Predicates precedes, overlaps, and contains on
  time intervals.
• Intersect can be applied on two intervals, to
  give a single (possibly empty) interval the
  union of two intervals may or may not be a single
  interval.
• A snapshot of a temporal relation at time t
  consists of the tuples that are valid at time t,
  with the time-interval attributes projected out.
• Temporal selection involves time attributes
• Temporal projection the tuples in the projection
  inherit their time-intervals from the tuples in
  the original relation.
• Temporal join the time-interval of a tuple in
  the result is the intersection of the
  time-intervals of the tuples from which it is
  derived. It intersection is empty, tuple is
  discarded from join.

30
TSQL

• Over 40 temporal datamodels have been studied
  over the last two decades. The clear winner has
  been TSQL, a temporal extension that is part of
  the SQL99 standard
• TSQL supports a bitemporal data model
• It provides condense representation of temporal
  queries
• The TSQL project is part of the ACM Anthology
• The language is illustrated through a few examples

31
TSQL case

• Consider a Patient database with records of
  information about prescribed drugs
• Create table Prescription (Name, Physician, Drug,
  Dosage,
• Frequency interval minute)
• as valid day and transaction
• A valid time specifies the period(s) during which
  the drug is prescribed
• The valid time granularity is a day
• The transaction time records when the information
  was added to the database
• The transaction time granularity is system
  defined.

32
TSQL case

• TSQL supports the following (temporal) tables
• Snapshot nothing after the attributes
• Valid-time state as valid stateltgranularitygt
• Valid-time event as valide eventltgranularitygt
• Transaction-time as transaction
• Bitemporal as valid stateltgranularitygt and
  transacion
• The type of the table can be altered at any time
• Semantics is not irreversable

33
TSQL example

• Who has been prescribed drugs?
• Select snapshot Name from Prescription
• Result in a list of names of those with current
  or past prescriptions
• Who is or was taking the drug Proventil?
• Select snapshot Name from Prescription where
  DrugProventil

34
TSQL examples

• Snapshot reducibility, converts a temporal
  relation to its ordinary RDBMS equivalent.
  Temporal components loose their additional
  meaning
• Who has been prescribed drugs, and when?
• Select Name From Prescription
• Results in a list of names, each associated with
  one or more maximal periods.

35
TSQL examples

• Snapshot reducibility, converts a temporal
  relation to its ordinary RDBMS equivalent.
  Temporal components loose their additional
  meaning
• What drugs have been prescribed with Proventil?
• Select P1.name, P2.drug
• From Prescription as P1, Prescription as P2
• Where P1.drug Proventil
• And P2.drug ltgt Proventil
• And P1.Name P2.Name
• Results in a list of patient names and drugs,
  along with their associated maximal periods.

36
TSQL examples

• Operators are provided to reason about validity
  property
• Who has been on drug for more than a total of 6
  months
• Select Name, Drug
• From Prescription(Name,Drug) as P
• Where cast(valid(P)) as interval month gtinterval
  6month
• Result will contain the maximal interval(s) when
  the patient has been on the drug
• Restructuring of tables as part of the from
  clause helps keeping queries concise

37
TSQL restructuring

• Restructuring is syntactic sugar
• From A(B,C) as A2
• Is equivalent to
• From (select B,C from A) as A2
• Nested select projects on the specified
  attributes and coalesces the result on its
  temporal properties
• Other attributes in A are not accessible via A2
  and A is not accessible in the enclosing select

38
TSQL example

• Who has been on Proventil throughout their drug
  regime?
• Select snapshot P1.Name
• From Prescription(Name) as P1, P1(Drug) as P2
• Where P2.Drug Proventil
• And valid(P2) valid(P1)
• P1 contains all the times that any drug has been
  prescribed to a patient
• P2 is coalesced on the Name and Drug columns

39
Valid time projections

• What drugs was Melanie prescribed during 1994?
• Select Drug
• Valid intersect(valid(Prescription), period
  1994day)
• From Prescription
• Where NameMelanie
• The result is a list of drugs, each associated
  with a set of periods during 1994 that they were
  prescribed to Melanie

40
Valid time modifications

• Insert into Prescription
• Values (Sally,dr Green,Proventil,100mg,
• Interval 800 minute
• Valid period 1993/01/01-1993/06/03
• Inserted values will be coalesced with existing
  value equivalent rows
• Default valid clause
• Valid period(current_timestamp,
• nobind(timestamp now))

41
Valid time modifications

• The delete statement removes period(s) from the
  temporal element of the qualifying rows
• Only if the temporal element becomes empty, the
  row is deleted
• Delete from Prescription
• Where NameMelanie
• Valid period 1993/06/01-now

42
Event tables

• Event tables are timestamped with instant sets
• Each row identifies a particular kind of
  (instantaneous) event, with the timestamp of the
  row specifying the instant(s) when that event
  occurred
• The attribute valid(P) now produces a set of time
  points
• Event tables may also be associated with
  transaction time

43
Transaction tables

• The transaction timestamp is a system defined
  counter
• If transaction time is not mentioned in the
  query, the results includee only the information
  currently believed to be true.
• Transaction tables can be used to rollback
• Select Drug
• From Prescription as P
• Where Name Melanie
• And transaction(P) overlaps date 1994

44
Temporal aggregation

• The standard aggregate operators are re-defined
  for TSQL to imply an automatic group on
  distinctive temporal periods.
• Idea break the tuples on the smallest possible
  non-overlapping periods, thereafter perform
  aggregate and coalesce the result back into
  periods.

45
Conclusion

• The possibilities to enhance the relational model
  with the notion of time has been widely studied
  (gt40 variants)
• A consensus model has been included in SQL99
• Time is an instantiation of a sequence, as in
  time-series. But temporal support to sequences is
  still rather mininal.
• how to extend the relational model with
  sequences?