CREATE TABLE Statement (ANSI Valid-Time Table Form) | Teradata Vantage - 17.00 - CREATE TABLE (ANSI Valid-Time Table Form) - Teradata Database

Teradata Vantage™ - ANSI Temporal Table Support

prodname
Advanced SQL Engine
Teradata Database
vrm_release
17.00
created_date
June 2020
category
Programming Reference
featnum
B035-1186-170K

Purpose

Create a new ANSI valid-time table.

Syntax

CREATE MULTISET TABLE table_name (
  valid_start { DATE | TIMESTAMP [ ( precision ) ] [ WITH TIME ZONE ] } NOT NULL,
  valid_end { DATE | TIMESTAMP [ ( precision ) ] [ WITH TIME ZONE ] } NOT NULL,
  PERIOD FOR valid_time_period ( valid_start, valid_end ) [AS] VALIDTIME
) [;]
table_name
The name of the valid-time table. May include database qualifier.
valid_start
The name of the column that will store the beginning bound of the valid-time period.
valid_end
The name of the column that will store the ending bound of the valid-time period.
precision
The precision of the timestamp value. The default is 6.
valid_time_period
The name of the valid-time derived period column.
[AS] VALIDTIME
Required to create valid-time tables in the database.
The [AS] VALIDTIME clause allows SELECT statements on valid-time tables to use special temporal qualifiers (AS OF, BETWEEN...AND, FROM...TO, CONTAINED IN), which ANSI does not support on valid-time tables. For more information see Querying ANSI Valid-Time Tables.
[AS] VALIDTIME is a Teradata extension to ANSI.

ANSI Compliance

This statement is ANSI SQL:2011 compliant, but includes non-ANSI Teradata extensions.

[AS] VALIDTIME is not ANSI compliant, but must be included in valid-time tables defined in the database.

Usage Notes

  • Valid time is the Teradata implementation of what ANSI calls “application time.”
  • The valid_start and valid_end columns must be defined as NOT NULL.
  • The data types of the valid_start and valid_end columns must match.
  • To function as a temporal table, the valid-time table must be defined AS VALIDTIME. System-time tables do not have valid time.
  • A table can have only one valid-time period definition.
  • A valid-time table cannot be a queue, error, or global temporary trace table.
  • Statistics cannot be collected on the valid-time derived period column, but they can be collected on the component start and end time columns.
  • Algorithmic compression (ALC) is not allowed on DateTime columns that act as the beginning and ending bound values of a temporal derived period column.

Example: Creating an ANSI Valid-Time Table

The following example creates a valid-time table.

CREATE MULTISET TABLE employee_valid_time (
 eid INTEGER NOT NULL,
 ename VARCHAR(5) NOT NULL,
 terms VARCHAR(5),
 job_start DATE NOT NULL,
 job_end DATE NOT NULL,
 PERIOD FOR job_dur(job_start,job_end) AS VALIDTIME
 )
 PRIMARY INDEX (eid);