15.00 - Temporal Table Queries - Teradata Database

Teradata Database Temporal Table Support

prodname
Teradata Database
vrm_release
15.00
category
Programming Reference
featnum
B035-1182-015K

Temporal Table Queries

Queries involving a temporal table with valid time can be current, sequenced, or nonsequenced. On a table with transaction time, temporal queries can be current or nonsequenced.

Current Temporal Query

A current query is a SELECT statement that extracts and operates on the current rows of a table:

  • For a transaction-time table, current queries operate only on open rows.
  • For a valid-time table, current queries operate only on rows with valid-time periods that overlap the current time.
  • For bitemporal tables, current queries operate only on rows that are both open in the transaction-time dimension an current in the valid-time dimension.
  • A current query produces a nontemporal table as a result set.

    Sequenced Temporal Query

    A sequenced query is a SELECT statement that extracts and operates on rows in a valid-time or bitemporal table with valid-time periods that overlap a time period specified in the query (the PA of the query). If no time period is explicitly specified in the query, the default PA is all time, and the query applies to all open rows in the table. Such queries can return rows that are history rows, current rows, future rows, or combinations of the three.

    A sequenced query produces a temporal table as a result set. The valid time of the result rows is the overlap of the query PA with the original row PV.

    As Of Query

    An as of query is a SELECT statement that extracts and operates on rows in temporal tables with valid-time and transaction-time periods that overlap an AS OF date or time specified in the query. As of queries operate on the data as a snapshot at any point in time. Typically, as of queries are used for querying historical data.

    The AS OF clause can be applied to the valid-time and transaction-time dimensions together or independently. When applied to the valid-time dimension, it retrieves rows where the PV overlaps the specified AS OF time. When applied to the transaction-time dimension, it retrieves rows with transaction-time periods that overlap the specified AS OF time.

    An as of query is similar in semantics to a current query; an AS OF extracts the information based on the specified time and a current query extracts the information as of the current time or date of the query being executed. However, a current query operates on only open rows in the transaction-time dimension. An AS OF query can read rows as of a particular point in time in the transaction-time dimension regardless of whether the rows are closed or open.

    An as of query produces a nontemporal table as a result set.

    Nonsequenced Temporal Query

    A nonsequenced query is a SELECT statement that treats temporal columns of temporal tables as if they were nontemporal columns. It does not place any special semantics on temporal columns. It considers all states simultaneously.

    A NONSEQUENCED VALIDTIME query can optionally include a PA. In this case, the query produces a valid-time temporal table, where the valid time of the result set rows is the PA specified in the query. If a NONSEQUENCED VALIDTIME query does not include a PA, the query produces a nontemporal table as a result set.

    A NONSEQUENCED TRANSACTIONTIME query cannot include a PA, and always produces a nontemporal table as a result set.

    Related Information

     

    For more information on...

    See...

    open, closed, current, future, and history rows

    “Temporal Row Types” on page 22.

    transaction time and valid time

    “Transaction Time and Valid Time” on page 17

    current, sequenced, as of, and nonsequenced queries

  • “SELECT/SELECT ... INTO (Temporal Forms)” on page 137
  • “Querying Temporal Tables” on page 197