CREATE TABLE Statement (ANSI Valid-Time Table Form) | Teradata Vantage - CREATE TABLE (ANSI Valid-Time Table Form) - Advanced SQL Engine - Teradata Database

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.