Teradata provides temporal table support at the data type level with period data types. A period is an anchored duration that represents a set of contiguous time granules within the duration. It has a beginning bound (defined by the value of a beginning element) and an ending bound (defined by the value of an ending element). Beginning and ending elements can be DATE, TIME, or TIMESTAMP types, but both must be the same type.
The duration that a period represents starts from the beginning bound and extends up to, but does not include, the ending bound.
If the element type is DATE or TIMESTAMP, the ending bound can have a special value of UNTIL_CHANGED, where Vantage interprets the ending bound of the period as forever, or without end.
As a first step toward adding temporal table support to the Policy table, the application for the insurance company can create the Policy table with a PERIOD(DATE) column to record when rows are valid.
CREATE TABLE Policy( Policy_ID INTEGER, Customer_ID INTEGER, Policy_Type CHAR(2) NOT NULL, Policy_Details CHAR(40), Validity PERIOD(DATE) ) PRIMARY INDEX(Policy_ID);
Although the Policy table is a nontemporal table, the application can use the built-in support that Vantage provides for period types, including period constructors, literals, operators, functions, and predicates.
For example, to add a row to the table, the application could use the period constructor to specify a value for the Validity column.
INSERT INTO Policy (Policy_ID, Customer_ID, Policy_Type, Policy_Details, Validity) VALUES (541008, 246824626, 'AU', 'STD-CH-345-NXY-00', PERIOD(DATE '2009-10-01', UNTIL_CHANGED));
To retrieve rows from the Policy table that became valid on a specific date, the application could use the BEGIN function like this:
SELECT * FROM Policy WHERE BEGIN(Validity) = DATE '2010-01-01';