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) and ALTER TABLE (ANSI Valid-Time Table Form).
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