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 |
|