CREATE TABLE Statement (ANSI Bitemporal Table Form) | Teradata Vantage - 17.00 - CREATE TABLE (ANSI Bitemporal 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 bitemporal table.

Syntax

There is no special temporal syntax for creating bitemporal tables. It simply combines the forms discussed in CREATE TABLE (ANSI System-Time Table Form) and CREATE TABLE (ANSI Valid-Time Table Form). Note that a bitemporal table also combines the rules, restrictions, and usage notes listed for both system-time and valid-time tables..

For example, a bitemporal table cannot be the source table for a CREATE TABLE ... AS statement, and statistics cannot be collected on the derived period columns of bitemporal tables.

All restrictions for system-time and valid-time columns described in Working With ANSI System-Time Tables and Working With ANSI Valid-Time Tables apply to bitemporal tables.

Example: Creating an ANSI Bitemporal Table

The following example creates an ANSI bitemporal table.

CREATE MULTISET TABLE employee_bitemporal (
 eid INTEGER NOT NULL,
 ename VARCHAR(5),
 deptno INTEGER 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,
 sys_start TIMESTAMP(6) WITH TIME ZONE NOT NULL 
           GENERATED ALWAYS AS ROW START,
 sys_end TIMESTAMP(6) WITH TIME ZONE NOT NULL 
           GENERATED ALWAYS AS ROW END,
 PERIOD FOR SYSTEM_TIME(sys_start,sys_end)
 )
 PRIMARY INDEX (eid) WITH SYSTEM VERSIONING;