15.10 - ANSI Temporal and Teradata Temporal - Teradata Database

Teradata Database ANSI Temporal Table Support

Teradata Database
Programming Reference

Teradata introduced support for creating and manipulating temporal tables before an ANSI/ISO standard had been developed. Consequently the original Teradata temporal tables and SQL syntax do not conform to the ANSI standard. When ANSI/ISO standards for temporal tables were approved, Teradata developed new, ANSI-compliant temporal tables and SQL syntax.

Both the ANSI compliant and original non-ANSI compliant versions of temporal tables are available in Teradata Database.

Use the following comparison to help determine which version of temporal tables best meets your requirements.

Note: Most temporal qualifiers and query syntax that is used with Teradata’s proprietary temporal tables can be used also on ANSI temporal tables.


ANSI Temporal Tables and Syntax

Teradata Temporal Tables and Syntax

ANSI/ISO compliant, with minor variations for valid-time (application-time) table definitions and Teradata Database extensions to allow temporal queries of valid-time tables.

Not ANSI/ISO compliant.

Temporal columns of temporal tables are derived dynamically from physical DateTime columns that store the beginning and ending bound values of the derived periods.

Temporal columns may be derived periods or may use Teradata Database Period data types, that allow a column to represent a duration. (Use of Period data types is allowed, but not recommended.)

Start and end columns that constitute temporal derived period columns are always implicitly projected in SELECT * queries.

Temporal columns, or start and end columns that constitute temporal derived period columns may or may not be projected, depending on the temporal query qualifier or the temporal qualifier that is set as the default for the session.

In a system-time table, the component begin and end timestamp columns of the SYSTEM_TIME derived period column can only be modified if system versioning is first removed from the table. Removing system versioning from a system-time table physically deletes all closed rows from the table, and renders the table a non-temporal table.

In a transaction-time table (analogous to an ANSI system-time table), the special NONTEMPORAL privilege and qualifier allow modification of transaction-time column values.

Default SELECT behavior is to qualify all rows of valid-time tables. Default behavior cannot be changed with session qualifiers.

Default SELECT behavior is to qualify only current rows of valid-time tables. Default behavior can be changed with session qualifiers.