FROM Clause (ANSI System-Time Table Form) | Teradata Vantage - FROM Clause (ANSI System-Time Table Form) - Advanced SQL Engine - Teradata Database

ANSI Temporal Table Support

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-22
dita:mapPath
ngt1556732962433.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1186
lifecycle
previous
Product Category
Teradata Vantage™

Purpose

Uses system-time dimension criteria to determine which rows in ANSI system-time tables are subject to a SELECT query.

Syntax

FROM system_time_table [
  FOR SYSTEM TIME {
    AS OF point_in_time |
    BETWEEN point_in_time_1 AND point_in_time_2 |
    FROM point_in_time_1 TO point_in_time_2 |
    CONTAINED IN ( point_in_time_1, point_in_time_2 )
  }
]
system_time_table
The system-time table being queried. The table must be a system-versioned table to be subject to temporal syntax.
AS OF
Qualifies rows in system-time tables that were open at a given point in time. Use the AS OF qualifier to query the table as it existed at the AS OF time.
Although the returned rows were open (active in the database) at the time specified by the query, they may have been closed before the query is submitted. Such rows will show a timestamp for the ending bound that is prior to 9999-12-31 23:59:59.999999+00:00. Closed rows in the results indicate the row was modified or deleted after the AS OF time.
point_in_time
point_in_time_1
 and point_in_time_2
A timestamp expression that can be a constant, scalar UDF, scalar subquery, or business calendar function that evaluates to a DATE or TIMESTAMP[(n)] [WITH TIME ZONE] value.

The expression can be any expression, including parameterized values and built-in functions such as CURRENT DATE, CURRENT_TIMESTAMP, TEMPORAL_DATE, or TEMPORAL_TIMESTAMP. The expression cannot reference any columns, but it can be a self-contained noncorrelated scalar subquery.

BETWEEN ... AND
Qualifies all rows with system-time periods that overlap or immediately succeed the period defined by point_in_time_1 and point_in_time_2.
This is not the commonly understood meaning of “between” because BETWEEN will qualify rows with system-time periods that begin before by point_in_time_1, and rows that start immediately after point_in_time_2 and extend beyond that. For a qualifier that reflects the commonly understood meaning of BETWEEN, use the CONTAINED IN qualifier.
FROM ... TO
Qualifies all rows with system-time periods that overlap the period defined by point_in_time_1 and point_in_time_2.
CONTAINED IN
Qualifies all rows with system-time periods that are between point_in_time_1 and point_in_time_2. The system-time period starts at or after point_in_time_1, and ends before or at point_in_time_2.
CONTAINED IN is a Teradata extension to ANSI.

ANSI Compliance

This statement is ANSI SQL:2011 compliant, but includes non-ANSI Teradata extensions.

Usage Notes

  • If no temporal qualifiers are used in the FROM clause, the default for system-time tables is to qualify all rows that are currently (at the time of the query) open.
  • BETWEEN ... AND, FROM ... TO, and CONTAINED IN all qualify queries of temporal tables according to a period of time. Any table rows that were active in the database, open in system time, at any time during the specified period (or immediately after it in the case of BETWEEN ... AND) are qualified for the query. The differences in the three temporal qualifiers are subtle, but allow you to define your query to qualify a precise set of rows.

Examples of FROM Clause

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.

Example: AS OF Query on an ANSI System-Time Table

The following AS OF query retrieves all table rows that were open and active at the point in time specified in the query, regardless of whether these rows are open or closed now.

SELECT * 
FROM employee_systime 
FOR SYSTEM_TIME AS OF TIMESTAMP'2005-01-01 00:00:01.000000-08:00';

 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
1003 SRK       111 2004-02-10 00:00:00.000000-08:00 2006-03-01 00:00:00.000000-08:00
1004 Fred      222 2002-07-01 12:00:00.350000-08:00 2005-05-01 12:00:00.350000-08:00
1005 Alice     222 2004-12-01 00:12:23.120000-08:00 2005-05-01 12:00:00.450000-08:00

Apart from the sys_end values, the data in the rows reflect the state of the table as it existed on 2005-01-01. At that time Fred and Alice belonged to deptno 222, and SRK was still employed (the SRK row had not yet been deleted).

Because the rows for Fred, Alice, and SRK have sys_end values less than 9999-12-31 23:59:59.999999+00:00, you know that this information from 2005-01-01 is no longer current. One or more changes to these rows occurred after the AS OF date, and the time of the first of those changes is reflected by the sys_end value. Rows with sys_end dates of 9999-12-31 23:59:59.999999+00:00 have not been changed or deleted since the AS OF date.

A query of the table AS OF 2005-05-02, would reflect a table query that occurred immediately after Fred and Alice had changed departments:

SELECT * 
FROM employee_systime 
FOR SYSTEM_TIME AS OF TIMESTAMP'2005-01-01 00:00:01.000000-08:00';

 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
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

Because AS OF queries reflect the state of the table at a given moment in time, they cannot indicate the nature of the changes that occurred to the closed rows that are returned. To determine the nature of changes to rows in a system-time table, you need to see the row as it existed both before and after the change. The remaining temporal query qualifiers, BETWEEN ... AND, FROM ... TO, and CONTAINED IN, allow you to specify spans of valid time, rather than instances. If, during the specified span, a row has been changed, both the old and new versions of the row are returned, which allows you to determine the nature of the change that caused the row to be closed in system time.

Example: BETWEEN ... AND Query on an ANSI System-Time Table

To see the nature of the changes to the Fred and Alice rows, you need a query that will return the rows as they existed both before and after the change. You could submit a query with a BETWEEN ... AND temporal qualifier that spans the time of the change. We know from the AS OF query that the change occurred at 2005-05-01.

SELECT * 
FROM employee_systime 
FOR SYSTEM_TIME BETWEEN TIMESTAMP'2005-04-30 00:00:00.000001-08:00' AND                         TIMESTAMP'2005-05-02 00:00:00.000001-08:00' 
WHERE ename='Fred' OR ename='Alice' 
ORDER BY ename;

 eid ename  deptno                        sys_start                          sys_end
---- ------ ------ -------------------------------- --------------------------------
1005 Alice     222 2004-12-01 00:12:23.120000-08:00 2005-05-01 12:00:00.450000-08:00
1005 Alice     555 2005-05-01 12:00:00.450000-08:00 9999-12-31 23:59:59.999999+00:00
1004 Fred      222 2002-07-01 12:00:00.350000-08:00 2005-05-01 12:00:00.350000-08:00
1004 Fred      555 2005-05-01 12:00:00.350000-08:00 9999-12-31 23:59:59.999999+00:00

From the results it is clear that from December 1st, 2004 through May 1st, 2005 Alice was in Department 222. From May 1st, 2005 until now, Alice has been in Department 555. Similarly, Fred started in Department 222 and was there from July 1st, 2002 until May 1st, 2005, after which his department also changed to 555, and remains 555 today. Perhaps all the personnel in Department 222 switched departments in 2005, or possibly the department number was changed.

Example: FROM ... TO Query on an ANSI System-Time Table

Temporal qualifiers that span a period of time can also be used to select all rows in a system-time table, including all open and closed rows, if the specified period is sufficiently broad.

SELECT * 
FROM employee_systime 
FOR SYSTEM_TIME FROM TIMESTAMP'1900-01-01 00:00:00.000001-08:00' TO 
CURRENT_TIMESTAMP;

 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
1005 Alice     555 2005-05-01 12:00:00.450000-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
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

This has returned all rows in the system-time table.