15.00 - Temporal Statements - Teradata Database

Teradata Database Temporal Table Support

prodname
Teradata Database
vrm_release
15.00
category
Programming Reference
featnum
B035-1182-015K

Temporal Statements

Using the period data type and the temporal column attributes in a CREATE TABLE statement, the application for the insurance company can create the Policy table as a temporal table with a valid-time column to record when rows are valid.

   CREATE MULTISET TABLE Policy(
      Policy_ID INTEGER,
      Customer_ID INTEGER,
      Policy_Type CHAR(2) NOT NULL,
      Policy_Details CHAR(40),
      Validity PERIOD(DATE) NOT NULL AS VALIDTIME
      )
   PRIMARY INDEX(Policy_ID);

The application can easily add a row to the table.

   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));

Similarly, the application can easily query the table. The result of the following query is the rows that are valid at the current time (where the value of the Validity column overlaps with the current time):

   CURRENT VALIDTIME SELECT * FROM Policy;

Related Information

 

For more information on...

See...

temporal table concepts

Chapter 2: “Basic Temporal Concepts.”

creating temporal tables

  • Chapter 3: “Creating Temporal Tables”
  • Chapter 4: “SQL Data Definition Language (Temporal Forms)”
  • manipulating temporal tables

  • Chapter 5: “SQL Data Manipulation Language (Temporal Forms)”
  • “Modifying Temporal Tables” on page 200
  • querying temporal tables

  • Chapter 5: “SQL Data Manipulation Language (Temporal Forms)”
  • “Querying Temporal Tables” on page 197