17.10 - FROM Clause 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

Rows with sys_end dates that are not 9999-12-31 23:59:59.999999+00:00 are closed in system time. They have either been logically deleted from the table or modified since they were first added to the table, but they remain in the table as a permanent record of prior states of the table. They can not participate in most SQL operations, such as UPDATEs and DELETEs, but using temporal qualifiers they can be retrieved from system-time tables.

In this case, the table shows that the department values (column deptno) for Fred and Alice were changed on 2005-05-01, which is the end date of the closed Fred and Alice rows and the start date of the new rows for them that contain the new information.

SRK also shows a sys_end date prior to 9999-12-31 23:59:59.999999+00:00, but because there is no open row remaining in the table for SRK, the sys_end date indicates when the row was (logically) deleted from the table.

A simple SELECT with no temporal qualifiers shows only the open, active rows in a system-time table. Open rows are indicated by system-time periods with ending bounds of 9999-12-31 23:59:59.999999+00:00.

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

This reflects the state of the information today, at the moment the query is processed. Temporal qualifiers allow you to see a snapshot of the table as it existed at any prior time, and return results that were true for former states of the table.