Adding a Transaction-Time Column to a Table - Analytics Database - Teradata Vantage

Example: Creating a Transaction-Time Column Based on Two Existing Timestamp Columns

If a nontemporal table has two pre-existing TIMESTAMP(6) WITH TIME ZONE columns that represent the beginning and end bounds of a period of time, you can add a derived period column to the table based on these columns. The derived period column can serve as a transaction-time column, converting the table to a temporal table.

Given a table that was originally created with the following DDL statement:

   CREATE MULTISET TABLE Policy(
      Policy_ID INTEGER,
      Customer_ID INTEGER,
      Policy_Type CHAR(2) NOT NULL,
      Policy_Details CHAR(40),
      Policy_Tx_Begin TIMESTAMP(6) WITH TIME ZONE NOT NULL,
      Policy_Tx_End TIMESTAMP(6) WITH TIME ZONE NOT NULL
      )
   PRIMARY INDEX(Policy_ID);

Prior to converting the table to a temporal table, existing constraints should be noted, then dropped. The table could be converted to a transaction-time table using the following statement:

   ALTER TABLE Policy
     ADD PERIOD FOR Policy_Tx_Duration (Policy_Tx_Begin,Policy_Tx_End) 
      AS TRANSACTIONTIME;

After creating the temporal table, the constraints that were dropped can be reapplied.

Example: Adding a Period Data Type Transaction-Time Column

Consider the following nontemporal table definition that lacks duration data:

   CREATE MULTISET TABLE Customer (
      Customer_Name VARCHAR(40),
      Customer_ID INTEGER,
      Customer_Address VARCHAR(80),
      Customer_Phone VARCHAR(12)
      )
   PRIMARY INDEX (Customer_ID);

The following statement adds a transaction-time column to the Customer table:

   ALTER TABLE Customer
   ADD Customer_Duration PERIOD(TIMESTAMP(6) WITH TIME ZONE) NOT NULL
      AS TRANSACTIONTIME;