17.10 - FROM Clause Syntax Elements (ANSI Valid-Time Table Form) - 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)
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.