17.10 - FROM Clause Syntax Elements (ANSI System-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)
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_1
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.