CS240A: Databases and Knowledge Bases Time Ontology and Representations

1
CS240A Databases and Knowledge BasesTime
Ontology and Representations

• Carlo Zaniolo
• Department of Computer Science University of
  California, Los Angeles
• Revised January 2003

Notes From Chapter 5 of Advanced Database
Systems by Zaniolo, Ceri, Faloutsos, Snodgrass,
Subrahmanian and Zicari. Morgan Kaufmann, 1997
2
Time Properties

• Structure
• Boundedness
• Density
• Time Data Types
• Time and Facts

3
Time Structure

• Linear Time
• Branching Time
• Directed Acyclic Graph
• Periodic/Cyclic Time Sunday. E.g., days of the
  week.
• Boundedness of Time
• From now on, we will assume a linear time
  structure.
• Boundedness
• Unbounded
• Time origin exists (bounded from the left)
• Bounded time (bounds on both ends)

4
Time Density

• Discrete
• Time line is isomorphic to the integers
• Timeline composed of a sequence of
  nondecomposable time periods of some fixed,
  minimal duration, termed chronons.
• Between each pair of chronons is a finite number
  of other chronons.
• Dense (difficult to implement)
• Time line is isomorphic to the rational numbers.
• Between any two chronons is an infinite number of
  other instants.
• Continuous (very difficult to implement)
• Time line is isomorphic to the real numbers.
• Between each pair of instants is a infinite
  number of other instants.
• A bounded discrete representation of time is the
  simplest option used in SQL-2 and most temporal
  DBs.

5
Time Datatype in SQL-2

• DATE four digits for the year and two for month
  and day. Multiple formats allowed
• E.g., 2001-12-08 or 12/08/2001 or 12.08.2001
• ISO, USA, EUR, JIS representations
  supported---DBA selects which one is used in
  specific system.
• Internal representation is the same, independent
  of external ones. The internal representation of
  a time is a string of 4 bytes (each packs 2
  decimal digits).
• TIME 2 digits for hour, 2 for minutes, and 2 for
  seconds (plus optional fractional digits---system
  dependent). E.g., 135000, 1350, 150 PM denote
  the same timeinternally three 2-packed decimal
  digits.

6
Time Datatype in SQL-2 (cont.)

• TIMESTAMP datetime with six fractional digits
  for the second field. A timestamp is a seven-part
  value (year, month, day, hour, minute, second,
  and microsecond) that designates a date and time
  as defined above, Time includes a fractional
  specification of microseconds. E.g.
  2001-01-05-13.01.59.000000
• The internal representation of a timestamp is a
  string of 10 bytes, each of which consists of 2
  packed decimal digits. The first 4 bytes
  represent the date, the next 3 bytes the time,
  and the last 3 bytes the microseconds.
• The length of a TIMESTAMP column, as described in
  the SQLDA, is 26 bytes, which is the appropriate
  length for the character string representation of
  the value.

7
Time Data Type in SQL-2 (cont.)

• TIME(STAMP) WITH ZONE offset according to UTC
  (universel temps coordonné)
• INTERVAL I.e. a time span. In DB2 is called a
  labeled duration. E.g. , 10 DAYS
• Time expressions. Using the labeled duration in
  arithmetic
• orderdate 10 DAYS lt CURRENT DATE --- this
  is valid, but
• CURRENT DATE - overdate gt 10 DAYS --- this
  is invalid.
• CAST expressions E.g. CAST(2 DAYS AS
  HOURS)returns 48 HOURS

8
Various Temporal Typesused in temporal DBs

• A time instant is a time point on the 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 set of time instants
  between two instants (start time and end time).
• In TSQL2, the basic temporal element is a finite
  union of periods.

9
Periods versus Time Intervals

• Periods are frequently called (time) intervals.
• but this conflicts with the SQL data type
  INTERVAL and we will try to avoid it.
• A SQL time interval is a directed duration of
  time. A duration is an amount of time with a
  known length, but no specific starting or ending
  instants.Also called a span.
• A positive interval denotes forward motion of
  time a negative interval denotes backwards
  motion of time.

10
Valid Time and Transaction Time

• Valid Time of a fact when the fact is true in
  the modeled reality
• Transaction Time of a fact when it was recorded
  in the database.
• Thus we have four different kinds of tables
• Snapshot
• Valid-time
• Transaction-time
• Bitemporal

11
Example Tom's Employment History

• On January 1, 1984, Tom joined the faculty as an
  Instructor.
• On December 1, 1984, Tom completed his doctorate,
  and so was promoted to Assistant Professor
  effective on July 1, 1984 (retroactive update).
• On March 1, 1989, Tom was promoted to Associate
  Professor, effective July 1, 1989 (proactive
  update).

12
Queries and Updates

• A transaction time table is append-only it
  keeps the history of the updates made on the
  database.
• Transaction time tables supports rollback
  queries, such as
• On October 1, what rank was our database showing
  for Tom?
• A valid time table can be updated e.g., Toms
  past record is changed once his rank is changed
  retroactively.
• Valid time tables support historical queries,
  such as
• What was Toms rank on October 1 (according to
  our current database)? Transaction time databases
  also can support historical queries.

13
Bitemporal Tables

• Bitemporal Tables are appendonly and supports
  queries of both kinds (rollbackhistorical) such
  as
• On October 1, 1984, what did we think Tom's rank
  was at that date?
• TSQL3
• SELECT Rank FROM Faculty AS F WHERE Name
  'Tom AND VALID(F) OVERLAPS DATE
  '19841001 AND TRANSACTION(F) OVERLAPS DATE
  '19841001'