Derived Period Columns and CREATE TABLE - Analytics Database - Teradata Vantage

Temporal Table Support

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-10-30
dita:mapPath
eud1628112402879.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
fif1472244754791
lifecycle
latest
Product Category
Teradata Vantage™
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, the system 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 the system 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.