In addition to the regular rules for the use of derived period columns, which are described in
Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144, the following rules and restrictions apply to the use of derived VALIDTIME and TRANSACTIONTIME columns in temporal tables.
- The component columns of a derived period column used as a temporal column cannot be part of the primary index.
- When a derived period column is defined as a TRANSACTIONTIME column, neither of the component columns can be set by the user to NULL, neither of the columns can have user-specified default value, and both must have a data type of TIMESTAMP(6) WITH TIME ZONE.
- All rules for regular VALIDTIME and TRANSACTIONTIME columns apply also to the component columns of a derived period column used as a temporal column.
- UNTIL_CHANGED can be specified as one of the values to be compressed by multivalue compression for end_column for a VALIDTIME derived period column.
- If the TRANSACTIONTIME column is a derived period column, and if data for the begin_column or end_column for imported data contains leap seconds, the seconds portion is adjusted to 59.999999 with the precision truncated to the described precision for the input data. During this process, if the beginning and ending bounds of a transaction-time period become the same, Teradata Database generates an error.
For example, if the a begin_column value is TIMESTAMP ‘2006-12-31 23:59:59.999’ and the corresponding end_column value is TIMESTAMP ‘2006-12-31 23:59.60.123’, the ending bound value is adjusted to TIMESTAMP ‘2006-12-31 23:59.59.999’ which is the same as the beginning bound, so Teradata Database generates an error.
- If the source table of a CREATE TABLE ... AS statement has any derived period columns, the target table that is created will have the same derived period columns.
- If a SEQUENCED qualifier is specified in a CREATE TABLE...AS subquery, and the source is a temporal table with derived period columns for VALIDTIME or TRANSACTIONTIME, the resulting target table is a temporal table with a period data type column acting as a VALIDTIME or TRANSACTIONTIME column.