17.10 - Examples - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - ANSI Temporal Table Support

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1186-171K
Language
English (United States)

For the following examples, assume the queries are issued against the following system-versioned system-time table named employee_systime that contains a mix of open and closed rows:

 eid ename  deptno                        sys_start                          sys_end
---- ------ ------ -------------------------------- --------------------------------
1002 Ash       333 2003-07-01 12:11:00.000000-08:00 9999-12-31 23:59:59.999999+00:00
1005 Alice     222 2004-12-01 00:12:23.120000-08:00 2005-05-01 12:00:00.450000-08:00
1004 Fred      222 2002-07-01 12:00:00.350000-08:00 2005-05-01 12:00:00.350000-08:00
1001 Sania     111 2002-01-01 00:00:00.000000-08:00 9999-12-31 23:59:59.999999+00:00
1003 SRK       111 2004-02-10 00:00:00.000000-08:00 2006-03-01 00:00:00.000000-08:00
1004 Fred      555 2005-05-01 12:00:00.350000-08:00 9999-12-31 23:59:59.999999+00:00
1005 Alice     555 2005-05-01 12:00:00.450000-08:00 9999-12-31 23:59:59.999999+00:00

In a table that has system time, only the open rows are subject to UPDATEs. A simple SELECT with no temporal qualifiers returns the open table rows:

SELECT * FROM employee_systime;

 eid ename  deptno                        sys_start                          sys_end
---- ------ ------ -------------------------------- --------------------------------
1002 Ash       333 2003-07-01 12:11:00.000000-08:00 9999-12-31 23:59:59.999999+00:00
1001 Sania     111 2002-01-01 00:00:00.000000-08:00 9999-12-31 23:59:59.999999+00:00
1004 Fred      555 2005-05-01 12:00:00.350000-08:00 9999-12-31 23:59:59.999999+00:00
1005 Alice     555 2005-05-01 12:00:00.450000-08:00 9999-12-31 23:59:59.999999+00:00

UPDATE employee_systime SET deptno=888 WHERE ename=’Ash’;

A simple SELECT shows the row has been changed as expected. Note that the start time for the system-time period records the time of the UPDATE, because it is from this time that the modification is known to the database and effective:

 eid ename  deptno                        sys_start                          sys_end
---- ------ ------ -------------------------------- --------------------------------
1002 Ash       888 2014-02-23 22:27:56.540000-08:00 9999-12-31 23:59:59.999999+00:00
1001 Sania     111 2002-01-01 00:00:00.000000-08:00 9999-12-31 23:59:59.999999+00:00
1004 Fred      555 2005-05-01 12:00:00.350000-08:00 9999-12-31 23:59:59.999999+00:00
1005 Alice     555 2005-05-01 12:00:00.450000-08:00 9999-12-31 23:59:59.999999+00:00

A temporal select that shows open and closed rows demonstrates that the original row still exists in the table. It has been closed, and the end boundary of the old row system time has been set to the time of the modification, which is the time when the values in that row ceased to apply or be active in the database.

SELECT * FROM employee_systime 
FOR SYSTEM_TIME BETWEEN TIMESTAMP'1900-01-01 22:14:02.820000-08:00' and CURRENT_TIMESTAMP 
WHERE ename=’Ash’;


 eid ename  deptno                        sys_start                          sys_end
---- ------ ------ -------------------------------- --------------------------------
1002 Ash       888 2014-02-23 22:27:56.540000-08:00 9999-12-31 23:59:59.999999+00:00
1002 Ash       333 2003-07-01 12:11:00.000000-08:00 2014-02-23 22:27:56.540000-08:00

In this way, a system-time table maintains a complete history of changes to the rows in the table.