17.05 - Syntax - 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
Teradata Vantage™ - Temporal Table Support describes syntax that is especially relevant to temporal tables. Syntax that is not required, or that is not otherwise specific to temporal tables is generally not shown in this document. For additional syntax, see Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144 , Teradata Vantage™ - SQL Data Manipulation Language, B035-1146 , and Teradata Vantage™ - SQL Data Control Language, B035-1149.
{ SET SESSION | SS }
  { valid_time_qualifier [ AND transaction_time_qualifier ] |

    transaction_time_qualifier [ AND valid_time_qualifier ] |

    AS OF date_timestamp_expression |

    ANSIQUALIFIER
  } [;]
valid_time_qualifier
{ CURRENT VALIDTIME |

  VALIDTIME AS OF date_timestamp_expression |

  { [ SEQUENCED | NONSEQUENCED ] VALIDTIME } [ period_expression ]
}
transaction_time_qualifier
{ { CURRENT | NONSEQUENCED } TRANSACTIONTIME |

  TRANSACTIONTIME AS OF date_timestamp_expression
}
CURRENT VALIDTIME
Specifies that the session valid-time qualifier is current.
VALIDTIME AS OF date_timestamp_expression
Specifies that when a SELECT statement refers to a temporal table but omits a valid-time qualifier, the default behavior extracts a snapshot of the table in the valid-time dimension AS OF the date or timestamp value specified by date_timestamp_expression.

date_timestamp_expression can be a constant, scalar UDF, scalar subquery, or business calendar function that evaluates to a date or timestamp value.

VALIDTIME SEQUENCED VALIDTIME
Specifies that the session valid-time qualifier is sequenced.
NONSEQUENCED VALIDTIME
Specifies that the session valid-time qualifier is nonsequenced.
period_expression
Specifies the period of applicability for the sequenced or nonsequenced session valid-time qualifier. The period value must be a constant expression or built-in function and cannot reference any columns.
CURRENT TRANSACTIONTIME
Specifies that the session transaction-time qualifier is current.
TRANSACTIONTIME AS OF date_timestamp_expression
Specifies that when a SELECT statement refers to a temporal table but omits a transaction-time qualifier, the default behavior extracts a snapshot of the table in the transaction-time dimension AS OF the date or timestamp value specified by date_timestamp_expression.

date_timestamp_expression can be a constant, scalar UDF, scalar subquery, or business calendar function that evaluates to a date or timestamp value.

NONSEQUENCED TRANSACTIONTIME
Specifies that the session transaction-time qualifier is nonsequenced.
AS OF date_timestamp_expression
Specifies that when a SELECT statement refers to a temporal table but omits the valid-time qualifier, transaction-time qualifier, or both, the default behavior extracts a snapshot of the table in the valid-time dimension, transaction-time dimension, or both dimensions AS OF the value specified by date_timestamp_expression.

date_timestamp_expression can be a constant, scalar UDF, scalar subquery, or business calendar function that evaluates to a date or timestamp value.

ANSIQUALIFIER
This is the default for users of ANSI/ISO-compatible temporal tables and syntax.
The session temporal qualifier must be ANSIQUALIFIER in order to create ANSI-compatible temporal tables and to get ANSI-compatible database behavior for temporal queries and DML.
The session qualifier must also be ANSIQUALIFIER in order to convert existing temporal tables described in this document to ANSI-compatible temporal tables described in Teradata Vantage™ - ANSI Temporal Table Support , B035-1186 . For more information on converting transaction-time tables to system-time tables, see ANSI Temporal Tables.