FROM Clause Syntax Elements (ANSI System-Time Table Form) - Analytics Database - Teradata Vantage

ANSI Temporal Table Support

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-10-30
dita:mapPath
jqu1628112571823.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
esa1472244798285
lifecycle
latest
Product Category
Teradata Vantageā„¢
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.