FROM Clause (ANSI Valid-Time Table Form) | Teradata Vantage - 17.00 - FROM Clause (ANSI Valid-Time Table Form) - Teradata Database

Teradata Vantage™ - ANSI Temporal Table Support

prodname
Advanced SQL Engine
Teradata Database
vrm_release
17.00
created_date
June 2020
category
Programming Reference
featnum
B035-1186-170K

Purpose

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

Syntax

FROM valid_time_table [
  [FOR] VALIDTIME {
    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 )
  }
]
valid_time_table
The valid-time table being queried.
AS OF
Qualifies rows in valid-time tables with valid-time periods that include the specified point in time. This means the information in the row is, was, or will be valid at the specified point in time.
Although the returned rows are valid at the time specified by the query, they may not be valid at the time the query is submitted.
point_in_time
point_in_time_1
point_in_time_2
Identifies the point in valid time or delimits the period in valid time for which rows will be returned.
A date or 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 valid-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 valid-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 valid-time periods that overlap the period defined by point_in_time_1 and point_in_time_2.
CONTAINED IN
Qualifies all rows with valid-time periods that are between point_in_time_1 and point_in_time_2. The valid-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 a Teradata extension to the ANSI SQL:2011 standard.

ANSI does not support AS OF, BETWEEN ... AND, and FROM ... TO temporal qualifiers for queries of valid-time tables.

Usage Notes

  • If no temporal qualifiers are used in the FROM clause, the default for valid-time tables is to qualify all rows in the table, regardless of validity.
  • If temporal qualifiers are used in the FROM clause, the valid-time start and end columns are not returned in the results.
  • BETWEEN ... AND, FROM ... TO, and CONTAINED IN all qualify queries of temporal tables according to a period of time. Any table rows that were valid 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

A simple SELECT on a valid-time table with no temporal qualifiers shows all rows in the table, regardless of the valid-time periods. Assume this valid-time table has a valid-time derived period column, job_dur, that represents the duration from job_start to job_end.

SELECT * FROM employee_systime;

 eid ename terms  job_start     job_end
---- ----- ----- ---------- -----------
1002 Ash    TA05 2003/01/01  2003/12/31
1005 Alice  TW11 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

Rows with job_end dates that are 9999-12-31 are valid indefinitely.

Notice two rows for Alice with non-overlapping dates, indicating that the terms for Alice’s job contract changed as of December, 1, 2005. Even though one row ends at 2005-12-01 and the other starts at 2005-12-01, the rows do not “overlap” because the true duration of the period derived from the job_start and job_end columns is considered to be inclusive of the beginning bound and exclusive of the ending bound.

Temporal qualifiers allow you to qualify rows that are, were, or will be valid at any time or during any period you specify.

Example: AS OF Queries on an ANSI Valid-Time Table

The following AS OF query retrieves all table rows that were valid at 2002-01-01.

SELECT * 
FROM employee_vt 
FOR VALIDTIME AS OF DATE’2002-01-01’;

 eid ename terms  job_start     job_end
---- ----- ----- ---------- -----------
1001 Sania  TW08 2002/01/01  2006/12/31
1004 Fred   PW12 2001/05/01  9999/12/31

Only Sania and Fred had jobs with valid-time periods that include 2002-01-01.

Temporal queries can specify times in the future to return the rows that will be, or will still be valid at a future time:

SELECT * 
FROM employee_vt 
FOR VALIDTIME AS OF DATE’2015-02-01’;

 eid ename terms  job_start     job_end
---- ----- ----- ---------- -----------
1005 Alice  PW11 2005/12/01  9999/12/31
1010 Mike   TW07 2015/01/01  2016/12/31
1004 Fred   PW12 2001/05/01  9999/12/31

Example: CONTAINED IN Query on an ANSI Valid-Time Table

A CONTAINED IN query will return rows that were valid only within a specified period. Rows with valid-time periods that may start within the specified period, but continue beyond the period are not returned.

SELECT * 
FROM employee_vt 
FOR VALIDTIME CONTAINED IN (DATE’2004-01-01’,DATE’2005-12-31’);

 eid ename terms  job_start     job_end
---- ----- ----- ---------- -----------
1005 Alice  TW11 2004/12/01  2005/12/01
1003 SRK    TM02 2004/02/10  2005/02/09

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

This query specifies the same period as the CONTAINED IN example, but in the case of a FROM ... TO qualifier, any rows with valid-time periods that overlap the specified time period will be returned.

SELECT *
FROM employee_vt
FOR VALIDTIME FROM DATE’2004-01-01’ TO DATE’2005-12-31’;

 eid ename terms  job_start     job_end
---- ----- ----- ---------- -----------
1005 Alice  TW11 2004/12/01  2005/12/01
1004 Fred   PW12 2001/05/01  9999/12/31
1005 Alice  PW11 2005/12/01  9999/12/31
1003 SRK    TM02 2004/02/10  2005/02/09
1001 Sania  TW08 2002/01/01  2006/12/31