Temporal Queries & Modifications | ANSI Temporal Table Support | Vantage - Temporal Queries and Modifications - 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™
Vantage supports temporal SQL that allows you to qualify queries based on the durations associated with the rows:
  • AS OF queries consider only those rows that are in effect as of a given point in time.
  • BETWEEN time1 AND time2 queries consider only rows with durations that overlap or immediately succeed a given time period.
  • FROM time1 TO time2 queries are similar to BETWEEN ... AND queries, but they do not include rows where the duration succeeds but does not overlap the given time period.
  • CONTAINED IN(time1, time2) queries qualify rows with durations that lie within the specified time period.

For example:

SELECT Customer_ID, Policy_type 
FROM Policy 
FOR VALIDTIME AS OF DATE’2009-11-24’;

Output:

Customer_ID  Policy_Type
-----------  -----------
  246824626  AU

Temporal DELETE and UPDATE modifications can include a FOR PORTION OF qualifier that specifies when during the duration of a row a change or deletion is in effect, so such changes need not apply to the whole row in an all-or-nothing fashion.

Teradata provides rich support for Period data types, including operators, functions, and predicates, which could be used to return similar results. However, Period data types, period functions (BEGIN, END, LAST and INTERVAL), and the period predicate operator MEETS, are not ANSI standard SQL.

Related Information

For more information on... See...
Period data types and derived period columns
  • Teradata Vantage™ - Data Types and Literals, B035-1143
  • Teradata Vantage™ - SQL Functions, Expressions, and Predicates, B035-1145