Temporal Columns | ANSI Temporal Table Support | Teradata Vantage - Temporal Columns - Advanced SQL Engine - Teradata Database

Teradata provides temporal table support at the column level using derived period columns. A period is an anchored duration that represents a set of contiguous time granules within the duration. It has a beginning and ending bound, defined by two DateTime type columns in the table.

In a temporal table, the values from the beginning and ending bound columns are combined in a third, derived column that represents the period of time, or duration they delimit.

Now the application for the insurance company can create the Policy table with a derived period column to record the period during which each policy (row) is valid.

CREATE TABLE Policy(
 Policy_ID INTEGER,
 Customer_ID INTEGER,
 Policy_Type CHAR(2) NOT NULL,
 Policy_Details CHAR(40),
 Policy_Begin DATE NOT NULL,
 Policy_End DATE NOT NULL,
 PERIOD FOR Policy_Duration(Policy_Begin,Policy_End) AS VALIDTIME
  )
 PRIMARY INDEX(Policy_ID);

The derived period column is used internally. The value for each row is created and maintained automatically by Vantage based on the table definition. Data is never inserted explicitly for the derived period column, and that column is never itself returned or queried.

INSERT INTO Policy 
 (Policy_ID,Customer_ID, Policy_Type, Policy_Details, 
  Policy_Begin, Policy_End)
 VALUES (541008,246824626,'AU','STD-CH-345-NXY-00',
         DATE'2009-10-01',DATE'2010-10-01');

SELECT * FROM Policy;

 Policy_ID  Customer_ID  Policy_Type  Policy_Details       Policy_Begin  Policy_End
-----------  -----------  -----------  ------------------- ------------  ----------
     541008    246824626  AU           STD-CH-345-NXY-00     2009/10/01  2010/10/01