ALTER TABLE (ANSI Bitemporal Table Form) - Teradata Database

Purpose  

Temporal syntax for ALTER TABLE allows you to create ANSI bitemporal tables from existing nontemporal tables, and combine the ALTER TABLE syntaxes for system-time and valid-time tables.

Adding valid time to the table involves these ALTER TABLE operations:

Adding or altering the two DateTime columns that will serve as the beginning and ending bounds of the valid-time period

Adding a valid-time derived period column to the table, and designating the derived column VALIDTIME

Adding system time to the table involves these ALTER TABLE operations:

Adding a SYSTEM_TIME derived period column to the table by specifying the columns that will serve as the beginning and ending bounds of the system-time period.

Adding or altering the columns that will serve as the beginning bound and ending bound of the system-time period. If these columns already exist in the table, special attributes must be added to them.

Adding system versioning to the table

Syntax  

There is no special syntax for altering tables to bitemporal tables. Use the combined special syntax that is discussed in “ALTER TABLE (ANSI System-Time Table Form)” on page 31 and “ALTER TABLE (ANSI Valid-Time Table Form)” on page 60.

Note: The table must be altered to add a valid-time dimension prior to adding the system-time dimension, because ALTER TABLE operations on system-time tables are severely restricted.

Example : ALTER TABLE to Convert a Nontemporal Table to an ANSI Bitemporal Table

The following SQL creates a regular nontemporal table, and inserts some rows:

CREATE MULTISET TABLE employee_bitemp (

 eid INTEGER NOT NULL,

 ename VARCHAR(5),

 deptno INTEGER NOT NULL,

 terms VARCHAR(5),

 job_start DATE NOT NULL,

 job_end DATE NOT NULL,

 sys_start TIMESTAMP(6) WITH TIME ZONE NOT NULL,

 sys_end TIMESTAMP(6) WITH TIME ZONE NOT NULL

 )

 PRIMARY INDEX (eid);

INSERT INTO employee_bitemp VALUES 
 (1001,'Sania',111,'TW08',DATE'2002-01-01',DATE'2006-12-31',   TIMESTAMP'2002-01-01 00:00:00.000000-08:00', 
 TIMESTAMP'2002-07-01 12:00:00.350000+00:00');

INSERT INTO employee_bitemp VALUES 
 (1004,'Fred',222,'PW12', DATE'2001-05-01',DATE'9999-12-31',   TIMESTAMP'2001-05-01 12:00:00.350000-08:00', 
  TIMESTAMP'9999-12-31 23:59:59.999999+00:00');

INSERT INTO employee_bitemp VALUES 
 (1002,'Ash',333,'TA05',DATE'2003-01-01',DATE'2003-12-31',   TIMESTAMP'2003-01-01 12:11:00.000000-08:00', 
  TIMESTAMP'9999-12-31 23:59:59.999999+00:00');

INSERT INTO employee_bitemp VALUES 
 (1003,'SRK',111,'TM02',DATE'2004-02-10',DATE'2005-02-10',   TIMESTAMP'2004-02-10 00:00:00.000000-08:00', 
  TIMESTAMP'2004-12-01 00:12:23.120000+00:00');

INSERT INTO employee_bitemp VALUES 
 (1005,'Alice',222,'TW10',DATE'2004-12-01',DATE'9999-12-31',   TIMESTAMP'2004-12-01 12:00:00.450000-08:00', 
  TIMESTAMP'9999-12-31 23:59:59.999999+00:00');

INSERT INTO employee_bitemp VALUES 
 (1010,'Mike',444,'TW07',DATE'2015-01-01',DATE'2016-12-31',   TIMESTAMP'2004-12-01 00:12:23.120000-08:00', 
  TIMESTAMP'9999-12-31 23:59:59.999999+00:00');

After rows have been inserted, an unqualified SELECT on the table returns all rows:

SELECT * FROM employee_bitemp;

 eid ename deptno terms   job_start     job_end                         sys_start                           sys_end
---- ----- ------ -----  ----------  ----------  --------------------------------  --------------------------------
1002 Ash      333  TA05  2003/01/01  2003/12/31  2003-01-01 12:11:00.000000-08:00  9999-12-31 23:59:59.999999+00:00

1005 Alice    222  TW10  2004/12/01  9999/12/31  2004-12-01 12:00:00.450000-08:00  9999-12-31 23:59:59.999999+00:00

1010 Mike     444  TW07  2015/01/01  2016/12/31  2004-12-01 00:12:23.120000-08:00  9999-12-31 23:59:59.999999+00:00

1001 Sania    111  TW08  2002/01/01  2006/12/31  2002-01-01 00:00:00.000000-08:00  2002-07-01 12:00:00.350000+00:00

1004 Fred     222  PW12  2001/05/01  9999/12/31  2001-05-01 12:00:00.350000-08:00  9999-12-31 23:59:59.999999+00:00

1003 SRK      111  TM02  2004/02/10  2005/02/10  2004-02-10 00:00:00.000000-08:00  2004-12-01 00:12:23.120000+00:00

The following ALTER TABLE statement adds valid time to the table:

ALTER TABLE employee_bitemp 

 ADD PERIOD FOR job_dur(job_start,job_end) AS VALIDTIME;

A simple select from the table still returns all rows, as it would from any valid-time table:

SELECT * FROM employee_bitemp;

 eid ename deptno terms   job_start     job_end                         sys_start                           sys_end
---- ----- ------ -----  ----------  ----------  --------------------------------  --------------------------------
1002 Ash      333  TA05  2003/01/01  2003/12/31  2003-01-01 12:11:00.000000-08:00  9999-12-31 23:59:59.999999+00:00

1005 Alice    222  TW10  2004/12/01  9999/12/31  2004-12-01 12:00:00.450000-08:00  9999-12-31 23:59:59.999999+00:00

1010 Mike     444  TW07  2015/01/01  2016/12/31  2004-12-01 00:12:23.120000-08:00  9999-12-31 23:59:59.999999+00:00

1001 Sania    111  TW08  2002/01/01  2006/12/31  2002-01-01 00:00:00.000000-08:00  2002-07-01 12:00:00.350000+00:00

1004 Fred     222  PW12  2001/05/01  9999/12/31  2001-05-01 12:00:00.350000-08:00  9999-12-31 23:59:59.999999+00:00

1003 SRK      111  TM02  2004/02/10  2005/02/10  2004-02-10 00:00:00.000000-08:00  2004-12-01 00:12:23.120000+00:00

The following ALTER TABLE statements add system time to the table:

ALTER TABLE employee_bitemp 

 ADD PERIOD FOR SYSTEM_TIME(sys_start,sys_end) 

 ADD sys_start TIMESTAMP(6) WITH TIME ZONE NOT NULL 
               GENERATED ALWAYS AS ROW START 

 ADD sys_end TIMESTAMP(6) WITH TIME ZONE NOT NULL 
               GENERATED ALWAYS AS ROW END;

ALTER TABLE employee_bitemp

 ADD SYSTEM VERSIONING;

Because the table now includes a system-time dimension, rows that had dates for sys_end that were not 9999-12-31 23:59:59.999999+0:0 are considered closed rows, logically deleted from the database, so now a simple select shows only four rows:

SELECT * FROM employee_bitemp;

 eid ename deptno terms   job_start     job_end                         sys_start                           sys_end
---- ----- ------ -----  ----------  ----------  --------------------------------  --------------------------------
1002 Ash      333  TA05  2003/01/01  2003/12/31  2003-01-01 12:11:00.000000-08:00  9999-12-31 23:59:59.999999+00:00

1005 Alice    222  TW10  2004/12/01  9999/12/31  2004-12-01 12:00:00.450000-08:00  9999-12-31 23:59:59.999999+00:00

1010 Mike     444  TW07  2015/01/01  2016/12/31  2004-12-01 00:12:23.120000-08:00  9999-12-31 23:59:59.999999+00:00

1004 Fred     222  PW12  2001/05/01  9999/12/31  2001-05-01 12:00:00.350000-08:00  9999-12-31 23:59:59.999999+00:00