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