17.10 - AS OF date_time_expression in Transaction-Time Queries - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Temporal Table Support

Advanced SQL Engine
Teradata Database
Release Number
Release Date
July 2021
Content Type
Programming Reference
Publication ID
English (United States)

date_timestamp_expression can be a constant, scalar UDF, scalar subquery, or business calendar function that evaluates to a date or timestamp value.

The data type of date_time_expression must be comparable with TIMESTAMP(6) WITH TIME ZONE. The following rules apply.

Data Type of date_time_expression Details
DATE The DATE value is cast to TIMESTAMP(6) WITH TIME ZONE and used for qualification. The time portion of the converted timestamp value is 00:00:00 (hh:mi:ss) in the session time zone. The row is qualified based on the UTC timestamp values.
TIMESTAMP[(n)] [WITH TIME ZONE] If the timestamp value has a coarser precision, it is converted to TIMESTAMP(6) WITH TIME ZONE and then the rows are qualified.
Any other data type The system reports an error.

References to a transaction-time column can appear anywhere in the scope of the query and anywhere in the query block, including a WHERE condition or JOIN condition.

The AS OF qualifier serves as an additional qualification criteria such that only rows with a transaction-time value that overlaps the given time are eligible to participate in the query. Thereafter, the query treats all the underlying tables as non-transaction-time tables. Operations such as joins, aggregation, set operations, and so forth are not impacted by this qualifier.

If date_timestamp_expression in the transaction-time dimension uses TEMPORAL_DATE or TEMPORAL_TIMESTAMP, the value of the built-in function evaluates to the time of the transaction. If date_timestamp_expression is a value that is in the future, the qualifier is as if it is current in the transaction-time dimension.

If the query involves a bitemporal or valid-time table, refer to the following topics for additional information that applies to the valid-time dimension: