17.05 - Usage Notes - Teradata Database

Teradata Vantage™ - Temporal Table Support

prodname
Advanced SQL Engine
Teradata Database
vrm_release
17.00
17.05
created_date
June 2020
category
Programming Reference
featnum
B035-1182-170K

A temporal qualifier can be specified for the outermost SELECT statement, in a derived table, view, join index and other DDL statements.

A temporal qualifier cannot be specified in a subquery. A subquery inherits the temporal qualifier of its parent query, with one exception. When the qualifier is NONSEQUENCED VALIDTIME period_expression, the subquery does not inherit the specified period of applicability.

The absence of a valid-time qualifier in the statement makes the query current in the valid-time dimension if no session valid-time qualifier is available. The absence of a transaction-time qualifier in the statement makes the query current in the transaction-time dimension if no session transaction-time qualifier is available.

The following table provides the meanings for the various combinations of qualifiers for the temporal form of a query.

Temporal SELECT and Options Meaning
  • SELECT
  • CURRENT VALIDTIME SELECT
  • CURRENT TRANSACTIONTIME SELECT
  • CURRENT VALIDTIME AND CURRENT TRANSACTIONTIME SELECT
Query is current in valid time and current in transaction time.
  • VALIDTIME SELECT
  • SEQUENCED VALIDTIME SELECT
  • VALIDTIME AND CURRENT TRANSACTIONTIME SELECT
  • SEQUENCED VALIDTIME AND CURRENT TRANSACTIONTIME SELECT
Query is sequenced in valid time and current in transaction time.
  • VALIDTIME period_expression SELECT
  • SEQUENCED VALIDTIME period_expression SELECT
  • VALIDTIME period_expression AND CURRENT TRANSACTIONTIME SELECT
  • SEQUENCED VALIDTIME period_expression AND CURRENT TRANSACTIONTIME SELECT
Query is sequenced in valid time and current in transaction time. The rows of interest in the valid-time dimension are all those rows whose valid time overlaps the specified period_expression.
  • NONSEQUENCED VALIDTIME SELECT
  • NONSEQUENCED VALIDTIME AND CURRENT TRANSACTIONTIME SELECT
Query is nonsequenced in valid time and current in transaction time.
  • NONSEQUENCED VALIDTIME period_expression SELECT
  • NONSEQUENCED VALIDTIME period_expression AND CURRENT TRANSACTIONTIME SELECT
Query is nonsequenced in valid time and current in transaction time. The result of the query is a valid-time result with the valid-time value set as the specified period_expression.
  • VALIDTIME AS OF date_timestamp_expression SELECT
  • VALIDTIME AS OF date_timestamp_expression AND CURRENT TRANSACTIONTIME SELECT
Query is AS OF in valid time and current in transaction time.
  • NONSEQUENCED TRANSACTIONTIME SELECT
  • CURRENT VALIDTIME AND NONSEQUENCED TRANSACTIONTIME SELECT
Query is nonsequenced in transaction time and current in valid time.
  • VALIDTIME AND NONSEQUENCED TRANSACTIONTIME SELECT
  • SEQUENCED VALIDTIME AND NONSEQUENCED TRANSACTIONTIME SELECT
Query is nonsequenced in transaction time and sequenced in valid time.
  • VALIDTIME period_expression AND NONSEQUENCED TRANSACTIONTIME SELECT
  • SEQUENCED VALIDTIME period_expression AND NONSEQUENCED TRANSACTIONTIME SELECT
Query is nonsequenced in transaction time and sequenced in valid time. The rows of interest in the tables with valid time support are those rows whose valid time overlaps the specified period_expression.
NONSEQUENCED VALIDTIME AND NONSEQUENCED TRANSACTIONTIME SELECT
Query is nonsequenced in transaction time and nonsequenced in valid time.
NONSEQUENCED VALIDTIME  period_expression  AND NONSEQUENCED TRANSACTIONTIME SELECT
Query is nonsequenced in transaction time and nonsequenced in valid time. The result of the query is a valid-time result with the valid-time value set to the specified period_expression.
VALIDTIME AS OF  date_timestamp_expression  AND NONSEQUENCED TRANSACTIONTIME SELECT
Query is nonsequenced in transaction time and AS OF in valid time.
  • TRANSACTIONTIME AS OF date_timestamp_expression SELECT
  • CURRENT VALIDTIME AND TRANSACTIONTIME AS OF date_timestamp_expression SELECT
Query is AS OF in transaction time and current in valid time.
  • VALIDTIME AND TRANSACTIONTIME AS OF date_timestamp_expression SELECT
  • SEQUENCED VALIDTIME AND TRANSACTIONTIME AS OF date_timestamp_expression SELECT
Query is AS OF in transaction time and sequenced in valid time.
  • VALIDTIME period_expression AND TRANSACTIONTIME AS OF date_timestamp_expression SELECT
  • SEQUENCED VALIDTIME period_expression AND TRANSACTIONTIME AS OF date_timestamp_expression SELECT
Query is AS OF in transaction time and sequenced in valid time. The rows of interest in the valid-time tables are those rows whose valid time overlaps the specified period_expression.
NONSEQUENCED VALIDTIME AND TRANSACTIONTIME AS OF  date_timestamp_expression  SELECT
Query is AS OF in transaction time and nonsequenced in valid time.
NONSEQUENCED VALIDTIME  period_expression  AND TRANSACTIONTIME AS OF  date_timestamp_expression  SELECT
Query is AS OF in transaction time and nonsequenced in valid time. The result of the query is a valid-time result with the valid-time value in the rows set to period_expression value.
  • VALIDTIME AS OF date_timestamp_expression AND TRANSACTIONTIME AS OF date_timestamp_expression SELECT
  • AS OF date_timestamp_expression SELECT
Query is AS OF in transaction time and AS OF in valid time.