Comparison of ANSI Temporal and Teradata Temporal | Teradata Vantage - ANSI Temporal and Teradata Temporal - Advanced SQL Engine - Teradata Database

Temporal Table Support

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-23
dita:mapPath
cjo1556732840654.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1182
lifecycle
previous
Product Category
Teradata Vantage™

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 Vantage.

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

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 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 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.