The following SQL creates a regular nontemporal table, and inserts some rows:
CREATE MULTISET TABLE employee_vt ( eid INTEGER NOT NULL, ename VARCHAR(5) NOT NULL, terms VARCHAR(5), job_start DATE NOT NULL, job_end DATE NOT NULL ) PRIMARY INDEX(eid);
After rows have been inserted, an unqualified SELECT on the table returns all rows:
SELECT * FROM employee_systime; eid ename terms job_start job_end ---- ----- ----- ---------- ----------- 1002 Ash TA05 2003/01/01 2003/12/31 1005 Alice TW10 2004/12/01 2005/12/01 1010 Mike TW07 2015/01/01 2016/12/31 1005 Alice PW11 2005/12/01 9999/12/31 1001 Sania TW08 2002/01/01 2006/12/31 1004 Fred PW12 2001/05/01 9999/12/31 1003 SRK TM02 2004/02/10 2005/02/10
The following ALTER TABLE statement changes the table into a valid-time temporal table:
ALTER TABLE employee_vt ADD PERIOD FOR job_dur(job_start,job_end) AS VALIDTIME;
Unlike for system-time tables, an unqualified SELECT of a valid-time table shows all rows in the table, regardless of whether their valid-time period is passed, current, or in the future:
SELECT * FROM employee_systime;
Output:
eid ename terms job_start job_end ---- ----- ----- ---------- ----------- 1002 Ash TA05 2003/01/01 2003/12/31 1005 Alice TW10 2004/12/01 2005/12/01 1010 Mike TW07 2015/01/01 2016/12/31 1005 Alice PW11 2005/12/01 9999/12/31 1001 Sania TW08 2002/01/01 2006/12/31 1004 Fred PW12 2001/05/01 9999/12/31 1003 SRK TM02 2004/02/10 2005/02/09
Automatic temporal behavior is evident in valid-time tables when using temporal qualifiers for SELECT queries, and for UPDATE, and DELETE modifications. For more information, see Querying ANSI Valid-Time Tables and Modifying Rows in ANSI Valid-Time Tables.