15.00 - SELECT/SELECT ... INTO (Temporal Forms) - Teradata Database

Teradata Database Temporal Table Support

prodname
Teradata Database
vrm_release
15.00
category
Programming Reference
featnum
B035-1182-015K

SELECT/SELECT ... INTO (Temporal Forms)

Purpose  

SELECT returns specific row data from a temporal table in the form of a result table.

SELECT … INTO selects at most one row from a temporal table and assigns the values in that row to local variables or parameters in stored procedures.

Syntax - SELECT

Note: Temporal Table Support describes syntax that is especially relevant to temporal tables. Syntax that is not required, or that is not otherwise specific to temporal tables is generally not shown in this manual. For additional syntax, see SQL Data Definition Language, SQL Data Manipulation Language, and SQL Data Control Language.

Syntax - SELECT … INTO

Note: To ensure application portability to future ANSI standards for temporal SQL, Teradata recommends explicit specification of all temporal qualifiers.

 

Syntax Element …

Specifies …

CURRENT VALIDTIME

that the query is current in the valid-time dimension.

At least one table referenced in the query, including tables or views or derived tables mentioned in the FROM clause of a subquery, must be a table that supports valid time.

VALIDTIME AS OF
date_timestamp_expression

that the query is AS OF in the valid-time dimension.

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

The expression can be any DATE or TIMESTAMP[(n)] [WITH TIME ZONE] expression, including parameterized values and built-in functions such as CURRENT_DATE or TEMPORAL_DATE, that does not reference any columns. One exception to this rule is that the expression can be a self-contained noncorrelated scalar subquery. A noncorrelated scalar subquery is always assumed to be nonsequenced in the time dimensions regardless of the temporal query qualifier.

If the date_timestamp_expression specifies TEMPORAL_DATE or TEMPORAL_TIMESTAMP, the values of these built-in functions evaluate to the time of the transaction.

At least one table referenced in the query, including tables or views or derived tables mentioned in the FROM clause of a subquery, must be a table that supports valid time.

VALIDTIME

SEQUENCED VALIDTIME

that the query is sequenced in the valid-time dimension.

At least one table referenced in the query, including tables or views or derived tables mentioned in the FROM clause of a subquery, must be a table that supports valid time.

A sequenced valid-time query results in a valid-time table. The valid-time period for each row in the result set is the overlap of original row valid-time with the period_expression specified in the query. The valid-time column in the result set a new column named VALIDTIME, which is automatically appended to the results.

If period_expression is omitted, the period of applicability for a sequenced query defaults to PERIOD'(0001-01-01, UNTIL_CHANGED)' where the data type is PERIOD(DATE) or PERIOD'(0001-01-01 00:00:00.000000+00:00, UNTIL_CHANGED)’ where the data type is PERIOD(TIMESTAMP). In these cases, the valid-time periods of the rows in the result set matches the valid-time periods of the original rows in the queried temporal table.

NONSEQUENCED VALIDTIME

that the query is nonsequenced in the valid-time dimension. The system does not associate any special meaning to the valid-time column. The query can use the valid-time column like any other column.

At least one table referenced in the query, including tables or views or derived tables mentioned in the FROM clause of a subquery, must be a table that supports valid time.

period_expression

the period of applicability for the DML statement.

The period of applicability must be a period constant expression that does not reference any columns, but can reference parameterized values and the TEMPORAL_DATE or TEMPORAL_TIMESTAMP built-in functions.

The period of applicability can also be a self-contained noncorrelated scalar subquery that is always nonsequenced in the time dimensions regardless of the temporal qualifier for the DML statement.

Note: If a period_expression is specified, the valid-time column cannot be specified or referenced anywhere in the query. If the valid-time column is a derived period column, the component columns cannot be specified or referenced anywhere in the query.

If period_expression is omitted, the period of applicability defaults to PERIOD'(0001-01-01, UNTIL_CHANGED)' for a PERIOD(DATE) valid-time column or PERIOD '(0001-01-01 00:00:00.000000+00:00, UNTIL_CHANGED)' for a PERIOD(TIMESTAMP(n) WITH TIME ZONE) valid-time column, where precision n and WITH TIME ZONE are optional.

For a nonsequenced query, the period of applicability can either be a period value expression that does not reference any column names or an alias name to a period column or period expression.

When an SELECT … INTO statement specifies period_expression, the number of elements in the INTO target list (that specifies the variables into which the selected values must be saved) must be one more than the projected elements in the select list. This additional target element saves the resulting period of validity of the output row.

AND

a keyword for specifying both a valid-time qualifier and a transaction-time qualifier.

CURRENT TRANSACTIONTIME

that the query is current in the transaction-time dimension.

At least one table referenced in the query, including tables or views or derived tables mentioned in the FROM clause of a subquery, must be a table that supports transaction time.

TRANSACTIONTIME AS OF date_timestamp_expression

that the query is AS OF in the transaction-time dimension.

At least one table referenced in the query, including tables or views or derived tables mentioned in the FROM clause of a subquery, must be a table that supports transaction time.

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

NONSEQUENCED TRANSACTIONTIME

that the query is nonsequenced in the transaction-time dimension.

In a nonsequenced query, the system does not associate any special meaning to the transaction-time column. The query can use the transaction-time column like any other column.

At least one table referenced in the query, including tables or views or derived tables mentioned in the FROM clause of a subquery, must be a table that supports transaction time.

AS OF date_timestamp_expression

the query is an AS OF query in both the valid-time and transaction-time dimensions.

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

All tables with valid time referenced in the query use the specified qualifier to qualify in the valid-time dimension. Similarly, all tables with transaction time referenced in the query use the qualifier to qualify in the transaction-time dimension.

At least one table referenced in the query must be a temporal table. If only valid-time tables and nontemporal tables are referenced, the AS OF qualifier is equivalent to specifying VALIDTIME AS OF date_timestamp_expression. If only transaction-time tables and nontemporal tables are referenced, the AS OF qualifier is equivalent to specifying TRANSACTIONTIME AS OF date_timestamp_expression. If both valid-time and transaction-time tables are referenced, the AS OF qualifier is equivalent to specifying VALIDTIME AS OF date_timestamp_expression AND TRANSACTIONTIME AS OF date_timestamp_expression.

select_statement

conventional SELECT statement syntax, with temporal table support enhancements to the FROM clause.

For details on FROM, see “FROM Clause (Temporal Form)” on page 160.

select_into_statement

conventional SELECT … INTO statement syntax, with temporal table support enhancements to the FROM clause.

For details on FROM, see “FROM Clause (Temporal Form)” on page 160.

Usage Notes

A temporal qualifier can be specified for the outermost SELECT statement, in a derived table, view, join index and other DDL statements.

A temporal qualifier cannot be specified in a subquery. A subquery inherits the temporal qualifier of its parent query, with one exception. When the qualifier is NONSEQUENCED VALIDTIME period_expression, the subquery does not inherit the specified period of applicability.

The absence of a valid-time qualifier in the statement makes the query current in the valid-time dimension if no session valid-time qualifier is available. The absence of a transaction-time qualifier in the statement makes the query current in the transaction-time dimension if no session transaction-time qualifier is available.

The following table provides the meanings for the various combinations of qualifiers for the temporal form of a query.

 

Temporal SELECT and Options

Meaning

  • SELECT
  • CURRENT VALIDTIME SELECT
  • CURRENT TRANSACTIONTIME SELECT
  • CURRENT VALIDTIME AND CURRENT TRANSACTIONTIME SELECT
  • Query is current in valid time and current in transaction time.

  • VALIDTIME SELECT
  • SEQUENCED VALIDTIME SELECT
  • VALIDTIME AND CURRENT TRANSACTIONTIME SELECT
  • SEQUENCED VALIDTIME AND CURRENT TRANSACTIONTIME SELECT
  • Query is sequenced in valid time and current in transaction time.

  • VALIDTIME period_expression SELECT
  • SEQUENCED VALIDTIME period_expression SELECT
  • VALIDTIME period_expression AND CURRENT TRANSACTIONTIME SELECT
  • SEQUENCED VALIDTIME period_expression AND CURRENT TRANSACTIONTIME SELECT
  • Query is sequenced in valid time and current in transaction time. The rows of interest in the valid-time dimension are all those rows whose valid time overlaps the specified period_expression.

  • NONSEQUENCED VALIDTIME SELECT
  • NONSEQUENCED VALIDTIME AND CURRENT TRANSACTIONTIME SELECT
  • Query is nonsequenced in valid time and current in transaction time.

  • NONSEQUENCED VALIDTIME period_expression SELECT
  • NONSEQUENCED VALIDTIME period_expression AND CURRENT TRANSACTIONTIME SELECT
  • Query is nonsequenced in valid time and current in transaction time. The result of the query is a valid-time result with the valid-time value set as the specified period_expression.

  • VALIDTIME AS OF date_timestamp_expression SELECT
  • VALIDTIME AS OF date_timestamp_expression AND CURRENT TRANSACTIONTIME SELECT
  • Query is AS OF in valid time and current in transaction time.

  • NONSEQUENCED TRANSACTIONTIME SELECT
  • CURRENT VALIDTIME AND NONSEQUENCED TRANSACTIONTIME SELECT
  • Query is nonsequenced in transaction time and current in valid time.

  • VALIDTIME AND NONSEQUENCED TRANSACTIONTIME SELECT
  • SEQUENCED VALIDTIME AND NONSEQUENCED TRANSACTIONTIME SELECT
  • Query is nonsequenced in transaction time and sequenced in valid time.

  • VALIDTIME period_expression AND NONSEQUENCED TRANSACTIONTIME SELECT
  • SEQUENCED VALIDTIME period_expression AND NONSEQUENCED TRANSACTIONTIME SELECT
  • Query is nonsequenced in transaction time and sequenced in valid time. The rows of interest in the tables with valid time support are those rows whose valid time overlaps the specified period_expression.

    NONSEQUENCED VALIDTIME AND NONSEQUENCED TRANSACTIONTIME SELECT

    Query is nonsequenced in transaction time and nonsequenced in valid time.

    NONSEQUENCED VALIDTIME period_expression AND NONSEQUENCED TRANSACTIONTIME SELECT

    Query is nonsequenced in transaction time and nonsequenced in valid time. The result of the query is a valid-time result with the valid-time value set to the specified period_expression.

    VALIDTIME AS OF date_timestamp_expression AND NONSEQUENCED TRANSACTIONTIME SELECT

    Query is nonsequenced in transaction time and AS OF in valid time.

  • TRANSACTIONTIME AS OF date_timestamp_expression SELECT
  • CURRENT VALIDTIME AND TRANSACTIONTIME AS OF date_timestamp_expression SELECT
  • Query is AS OF in transaction time and current in valid time.

  • VALIDTIME AND TRANSACTIONTIME AS OF date_timestamp_expression SELECT
  • SEQUENCED VALIDTIME AND TRANSACTIONTIME AS OF date_timestamp_expression SELECT
  • Query is AS OF in transaction time and sequenced in valid time.

  • VALIDTIME period_expression AND TRANSACTIONTIME AS OF date_timestamp_expression SELECT
  • SEQUENCED VALIDTIME period_expression AND TRANSACTIONTIME AS OF date_timestamp_expression SELECT
  • Query is AS OF in transaction time and sequenced in valid time. The rows of interest in the valid-time tables are those rows whose valid time overlaps the specified period_expression.

    NONSEQUENCED VALIDTIME AND TRANSACTIONTIME AS OF date_timestamp_expression SELECT

    Query is AS OF in transaction time and nonsequenced in valid time.

    NONSEQUENCED VALIDTIME period_expression AND TRANSACTIONTIME AS OF date_timestamp_expression SELECT

    Query is AS OF in transaction time and nonsequenced in valid time. The result of the query is a valid-time result with the valid-time value in the rows set to period_expression value.

  • VALIDTIME AS OF date_timestamp_expression AND TRANSACTIONTIME AS OF date_timestamp_expression SELECT
  • AS OF date_timestamp_expression SELECT
  • Query is AS OF in transaction time and AS OF in valid time.

    Asterisks in Select Lists

    For nontemporal tables, an asterisk (*) in a select list causes all table columns to be returned by the SELECT statement. For temporal tables with any qualifier other than nonsequenced, only the nontemporal columns from the original table are returned. To have temporal columns from the original table returned with SELECT statements that use the asterisk to return all nontemporal columns, preface the asterisk with the table name and a period, and follow the asterisk with a comma, then list the temporal columns (or component columns of a temporal column defined as a derived period type) to be returned with the nontemporal columns.

    Examples  

    SELECT *
    FROM policy;

    Policy_ID  Customer_ID  Policy_Type  Policy_Details
    ---------  -----------  -----------  ------------------
       541077    766492008  AU           STD-CH-344-YXY-00
       541008    246824626  AU           STD-CH-345-NXY-00
       541145    616035020  AU           STD-CH-348-YXN-01


    SELECT policy.*, validity
    FROM Policy;

    Policy_ID  Customer_ID  Policy_Type  Policy_Details    Validity
    ---------  -----------  -----------  ----------------- ------------------------
    541077     766492008    AU           STD-CH-344-YXY-00 ('09/12/21', '99/12/31')
    541008     246824626    AU           STD-CH-345-NXY-00 ('09/10/01', '99/12/31')
    541145     616035020    AU           STD-CH-348-YXN-01 ('09/12/03', '10/12/01')

    Note: Valid-time columns cannot be referenced in SEQUENCED VALIDTIME queries that include a period of applicability.

    Current Valid-Time Queries

    A current valid-time query on a table with valid time considers only open rows where the period of validity overlaps with TEMPORAL_DATE or TEMPORAL_TIMESTAMP in the valid-time dimension. Such rows are called current rows of a table with valid time.

    Current valid-time queries on tables with valid time produce snapshot tables as result sets.

    The following rules apply to current valid-time queries on valid-time tables.

  • If the session valid-time qualifier is implicitly or explicitly set to current, a conventional SELECT statement that does not specify a temporal qualifier is current in the valid-time dimension for a valid-time table. If the query references a valid-time table, the SELECT can specify an optional CURRENT VALIDTIME.
  • A current query can reference the valid-time column anywhere in the query. For valid-time columns defined using derived period columns, the query can reference the component columns that define the valid time. The valid-time column is treated as a conventional Period column, or for valid-time columns that are derived, the component columns are treated as conventional DateTime columns. All conditions, including those specified on valid-time columns, apply only to the current rows. Temporal column references can appear in conditions to further filter the output.
  • An asterisk ( * ) in the projection list includes nontemporal columns only.
  • Current query processing is as follows:
  • a Extract the current rows of each of the valid-time tables specified in the query and treat the query as if it is specified on a nontemporal table. The resulting table, regardless of whether the projection list includes the valid-time column, is a nontemporal table without the valid-time dimension.

    b Execute the query as if it were a conventional query that was issued on tables without valid time.

    Because current query processing considers a snapshot of valid time, all operations, such as joins and aggregations, are the same as they are for conventional queries.

    A current query supports the join of two temporal tables of the same or differing valid-time granularities.

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

  • “Current Transaction-Time Queries” on page 155
  • “As Of date_time_expression in Transaction-Time Queries” on page 156
  • “Nonsequenced Transaction-Time Queries” on page 157
  • Caution:

    CURRENT DML modifications can cause serializability issues for concurrent transactions. See Appendix C: “Potential Concurrency Issues with Current Temporal DML” for information on avoiding these issues.

    Changing the Behavior of CURRENT VALIDTIME SELECT

    CURRENT VALIDTIME SELECT statements normally qualify rows for selection by choosing rows where the valid time overlaps TEMPORAL_TIMESTAMP. Within the transaction that contains the SELECT statement, TEMPORAL_TIMESTAMP reflects the time the transaction was begun, and remains fixed throughout the transaction.

    For lengthy transactions this behavior may not be desirable, because rows inserted or changed after the transaction has begun would not be selected. Such rows would have a valid-time period that begins after TEMPORAL_TIMESTAMP, and would therefore be considered future rows, not current rows. For example, if another user adds a row to a table after a transaction has begun, and the transaction performs a CURRENT VALIDTIME SELECT, the new row would not be selected.

    The following statement changes the behavior of CURRENT VALIDTIME SELECT statements to qualify rows for selection according to whether the valid time of the row overlaps CURRENT_TIMESTAMP. This allows the SELECT to match rows with the latest timestamps.

    Because CURRENT_TIMESTAMP is not fixed at the time the transaction was begun, this causes CURRENT VALIDTIME SELECT statements to match even the latest rows that were added or changed after the transaction containing the SELECT statement was begun.

    This setting affects SELECT statements and subqueries, derived tables, and views within those SELECT statements. It has no effect on other types of DML, or on subqueries, derived tables, and views within those DML statements. The setting is in effect until it is explicitly disabled using the NOT form of the statement.

    Note: Cached SELECT statements are not affected by this diagnostic statement.

    As Of date_time_expression in Valid-Time Queries

    When the AS OF clause is specified as a temporal qualifier either explicitly in the statement or implicitly as a session temporal qualifier, its usage covers the entire query. (The AS OF clause can also be specified in the FROM clause, but such usage covers only the corresponding table in the FROM clause. For details, see “FROM Clause (Temporal Form)” on page 160.)

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

    The following rules apply to As Of valid-time queries on valid-time tables:

  • As Of valid-time queries on valid-time tables produce snapshot tables as results.
  • When the AS OF temporal qualifier is specified in the valid-time dimension, it applies to all valid-time tables in the query. The valid-time columns of the valid-time tables are in the scope of the query and they can be used anywhere in the query block, including the WHERE condition and JOIN condition.
  • The behavior of a query with an AS OF temporal qualifier is as if a current query was issued at the specified AS OF time. However, a current query reads only valid rows and an As Of query can read rows that are no longer valid.
  • Specifying the AS OF qualifier in the valid-time dimension serves as an additional qualification criteria that only rows with a period of validity that overlaps the specified time are eligible to participate in the query. Thereafter, the query treats all the underlying tables as non-valid-time tables. Operations such as joins, aggregations, and set operations are not impacted by this qualifier.
  • The data type of date_time_expression must be comparable with the element type of the valid-time columns. The following rules apply.
  •  

    Data Type of date_time_expression

    Element Type of Temporal Column

    Details

    DATE

    DATE

    The data types are comparable.

    DATE

    TIMESTAMP[(n)] [WITH TIME ZONE]

    The DATE value is cast to TIMESTAMP(n) 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]

    DATE

    The temporal column value is cast to TIMESTAMP 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]

    TIMESTAMP[(m)] [WITH TIME ZONE]

    The timestamp value with coarser precision is converted to the finer precision and then the rows are qualified.

    Any other data type

     

    DATE

    Teradata Database reports an error.

     

    TIMESTAMP[(n)] [WITH TIME ZONE]

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

  • “Current Transaction-Time Queries” on page 155
  • “As Of date_time_expression in Transaction-Time Queries” on page 156
  • “Nonsequenced Transaction-Time Queries” on page 157
  • Sequenced Valid-Time Queries

    Sequenced temporal queries allow the extraction of the past, current, or future sequence of states of a temporal table. A query that is sequenced in valid time spans those rows with a period of validity that overlaps the period of applicability of the query. Additional conditions can be specified on the valid-time column to further filter the rows as required.

    A query that is sequenced in valid time extracts the states of the tables at each point of time as specified in the period of applicability. The resulting table is a valid-time table. The query is over one or more valid-time tables and produces a valid-time result.

    Rows having NULL in the valid-time column are not in the result.

    The result set includes a new column named VALIDTIME, which is automatically appended by the system. VALIDTIME shows the valid time of the rows in the result set of the query. This is different from the valid times that were originally defined for the rows. Because a sequenced query specifies a particular time period of applicability, the period of validity of the results is limited by that period of applicability. Therefore, the valid time of each row in the result set is the intersection of the period of applicability of the query with the periods of validity of the qualified rows.

    Example  

    SEQUENCED VALIDTIME PERIOD '(2009-01-01, 2009-12-31)'
    SELECT *
    FROM Policy;
    Policy_ID Customer_ID Policy_Type Policy_Details      VALIDTIME
    --------- ----------- ----------- ------------------- ------------------------
       541077   766492008 AU          STD-CH-344-YXY-00   ('09/12/21', '09/12/31')
       541008   246824626 AU          STD-CH-345-NXY-00   ('09/10/01', '09/12/31')
       541145   616035020 AU          STD-CH-348-YXN-01   ('09/12/03', '09/12/31')

    Although VALIDTIME is the valid-time column of the result set, Validity is the valid-time column of the originally queried Policy table. To show the Validity column in the results requires a subquery, because the valid-time column name cannot appear anywhere in a query that includes a PA. Use a sequenced validtime subquery that does not specify a PA. Because the Validity column is not the valid-time column of the derived table, it can be retrieved using a sequenced outer query that includes a PA:

    Example  

    SEQUENCED VALIDTIME PERIOD '(2009-01-01, 2009-12-31)' 
    SELECT Policy_ID, Customer_ID, Validity FROM (
       SEQUENCED VALIDTIME SELECT Policy.*, Validity 
       FROM Policy) AS my_derived_table;
    Policy_ID   Customer_ID  Validity                 VALIDTIME
    ----------- -----------  -----------------------  ------------------------
         541077   766492008  ('09/12/21', '99/12/31') ('09/12/21', '09/12/31')
         541008   246824626  ('09/10/01', '99/12/31') ('09/10/01', '09/12/31')
         541145   616035020  ('09/12/03', '10/12/01') ('09/12/03', '09/12/31')

    Comparing the Validity and VALIDTIME columns demonstrates that the valid-time period of the result set is the intersection of the valid-time period in the original Policy table Validity column with the PA specified in the sequenced select statement.

    This example uses the SELECT * asterisk notation in combination with explicit specification of a temporal column. For more information on the asterisk see “Asterisks in Select Lists” on page 144

    Other clauses of the SELECT statement, such as WHERE, cannot reference the new VALIDTIME column, and VALIDTIME cannot be used as an alias for any other projected columns.

    If a view or a derived table results in a valid-time table, it is treated like any other valid-time table specified in the query. Otherwise, it is treated like a nontemporal table.

    The sequenced form of a temporal query is limited to a simple select from a single valid-time table or a simple select with inner joins from multiple tables. A noncorrelated scalar subquery can be used in the temporal query.

    The following are not supported for sequenced queries:

  • Outer joins
  • Set operations
  • Ordered analytic functions
  • Subqueries other than noncorrelated scalar subqueries
  • WITH, WITH RECURSIVE, TOP n, DISTINCT
  • The following rules apply to sequenced inner joins:

  • The term row or rows mentioned in the context of sequenced inner joins implies all the rows of a table where the period of validity overlaps with the period of applicability of the query and satisfies any specified single table conditions.
  • The joined row contains the projected columns in the specified order and a valid-time column whose value is set to the result of the intersection of the period of applicability, the period of validity of the left row, and the period of validity of the right row.
  • If the valid-time column of each table involved in the join has the same granularity as the period of applicability, the granularity of the resulting period of validity is the same as the granularity of the period of applicability. Otherwise, the granularity of the result period of validity is the finest amongst them and each valid-time column is implicitly converted to the finest valid-time granularity before the join is performed.
  • For example, consider a sequenced join with a PERIOD(DATE) period of applicability, a table with a PERIOD(TIMESTAMP(3)) valid-time column, and a table with a PERIOD(TIMESTAMP(5)) valid-time column. The resulting VALIDTIME column has a PERIOD(TIMESTAMP(5)) data type.

    The granularity difference does not usually cause an issue. However, it can be confusing for some types of projected columns. For example a column that represents charge per day in the original table can be confusing when it is projected in a result having a validity of hours. Such cases can be avoided if tables with similar granularities are joined, or the data model specifies the same granularity for tables that are likely to be joined.

    Similarly if a non-temporal table is joined with a temporal table, the resulting rows reflect the validity of the temporal table. This validity can be misleading if applied to data that was not originally of a temporal nature.

  • For a sequenced inner join, the left row is joined with a matching row from the right table only if the periods of validity of the rows overlap.
  • A sequenced query with an ORDER BY clause may specify ordering on the resulting VALIDTIME column. To specify this column in the ORDER BY clause, use the VALIDTIME keyword or use its name delimited by double quotation marks ("VALIDTIME"). If the ordering clause does not specify VALIDTIME, the resulting VALIDTIME column is automatically made a part of the ORDER BY list as the last element in the list with ASC (ascending) default ordering

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

  • “Current Transaction-Time Queries” on page 155
  • “As Of date_time_expression in Transaction-Time Queries” on page 156
  • “Nonsequenced Transaction-Time Queries” on page 157
  • Aggregate Functions in Sequenced Queries

    The result of a sequenced valid-time query is a temporal table with rows that include a VALIDTIME column. The VALIDTIME column shows the valid time, the time for which the row information is valid, for the rows in the result set. This result set valid time is the intersection of the period of applicability of the query with the periods of validity of the qualified rows in the original queried table.

    You can include aggregate functions in sequenced valid-time queries. The aggregation is performed on every distinct duration defined by the combination of VALIDTIME periods in the result set.

    Note: SEQUENCED TRANSACTIONTIME aggregations are not supported.

    Usage Notes and Cautions

    A valid-time table represents state information that is considered valid for a specified duration, the valid time of the row. The valid data can be constant over the period of time, or may represent values that accumulate over the duration of the valid time period. Examples of state information would be:

  • A quantity of an inventory item that represents the amount of the item in stock during a specified period.
  • An insurance policy that has a specific duration of time for which it is valid.
  • The number of hours worked per unit time, such as hours per day worked on a project, where the valid time is expressed in units of whole days, using a PERIOD (DATE) data type.
  • It is important to carefully evaluate and choose the columns to which aggregations are applied in temporal tables. If an aggregation over time is applied to a non-state column, the results can be misleading and inaccurate.

  • When used on valid-time temporal tables, aggregations such as SUM, AVG, MIN, and MAX should be performed only on state columns. Using such aggregate functions on non-state columns can lead to meaningless or misleading results.
  • The COUNT aggregation is generally safe to perform and gives straightforward results. However, you should ensure that the results are interpreted in an appropriate way that corresponds to the columns used for the aggregation.
  • Example  

    Consider the following information about three jobs performed by an aircraft service company:

     

    ID

    Job_Type

    Charge

    Duration

    NumWorkersAssigned

    123

    Wing

    80

    4 Jan 2011 – 8 Jan 2011

    5

    123

    Fuselage

    20

    5 Jan 2011 – 7 Jan 2011

    3

    123

    Landing Gear

    6

    6 Jan 2011 – 9 Jan 2011

    1

    If the information is stored in a temporal table, where duration is the valid time column, the valid times for all the rows can be laid out like this:

    Jan 4 5 6 7 8 9
        |-------|
          |---|
            |-----|

    Aggregations in sequenced queries partition the combined valid-times of all rows into overlapping and non-overlapping durations. In the example, the valid time periods describe five distinct durations, defined by the boundaries of all the valid-time periods for all the rows in the table:

    Jan 4 5 6 7 8 9
        |-|-|-|-|-|

    Jan 4-5, 5-6, 6-7, 7-8, and 8-9.

    A sequenced query of the whole table (with no WHERE clause qualification) would return aggregations across each of the five distinct durations:

    SEQUENCED VALIDTIME
    SELECT id, COUNT(*) jobcount
    FROM aircraft_service
    GROUP BY 1
    ORDER BY VALIDTIME;

    Returns:

     

    ID

    Jobcount

    VALIDTIME

    123

    1

    ('11/01/04', '11/01/05')

    123

    2

    ('11/01/05', '11/01/06')

    123

    3

    ('11/01/06', '11/01/07')

    123

    2

    ('11/01/07', '11/01/08')

    123

    1

    ('11/01/08', '11/01/09')

    Note: If you use an ORDER BY VALIDTIME clause with a sequenced valid-time query that includes an aggregation, the ordering takes into account the generated VALIDTIME distinct durations that were calculated to perform the aggregation.

    If you use a GROUP BY VALIDTIME clause, the grouping does not use the distinct durations, but instead uses the same VALIDTIME values assigned to the results of nonaggregate queries: the intersection of the period of applicability of the query with the periods of validity of the qualified rows in the original queried table.

    Example  

    MIN and MAX functions can be used in sequenced aggregations with temporal tables, however it is important to apply them to appropriate columns. For the aircraft service example, the information in the Charge column is not a state value. Applying MIN and MAX to this column in a time dependent sequenced aggregation would give results that are easily misinterpreted or not meaningful.

    However, it would be meaningful to ask for the minimum and maximum number of workers assigned to all jobs combined at any time:

    SEQUENCED VALIDTIME
    SELECT id, 
           min(NumWorkersAssigned) as Minworkers, 
           max(NumWorkersAssigned) as Maxworkers 
    FROM aircraft_service
    GROUP BY 1
    ORDER BY VALIDTIME;
     

    ID

    Minworkers

    Maxworkers

    VALIDTIME

    123

    5

    5

    ('11/01/04', '11/01/05')

    123

    3

    5

    ('11/01/05', '11/01/06')

    123

    1

    5

    ('11/01/06', '11/01/07')

    123

    1

    5

    ('11/01/07', '11/01/08')

    123

    1

    1

    ('11/01/08', '11/01/09')

    Example  

    SUM and AVG aggregations can be used over state columns of valid-time temporal tables. In our aircraft service example, the information in the NumWorkersAssigned column is a state value, valid for the duration specified in the valid-time column. It is meaningful to ask about the total or average number of workers assigned to an aircraft, as in the following example.

    SEQUENCED VALIDTIME
    SELECT id,
           SUM (NumWorkersAssigned) TotalWorkersAssigned,
           AVG (NumWorkersAssigned) AvgWorkersAssigned,
    FROM aircradt_service
    GROUP BY 1
    ORDER BY VALIDTIME;
     

    ID

    TotalWorkersAssigned

    AvgWorkersAssigned

    VALIDTIME

    123

    5

    5

    ('11/01/04', '11/01/05')

    123

    8

    4

    ('11/01/05', '11/01/06')

    123

    9

    3

    ('11/01/06', '11/01/07')

    123

    6

    3

    ('11/01/07', '11/01/08')

    123

    1

    1

    ('11/01/08', '11/01/09')

    Example  

    To use aggregate functions meaningfully on the charge column, it would need to be expressed as a cumulative value that matches the granularity of the valid time column. For example, if the charge for each aircraft service job is expressed, in this case, as a charge per day, sequenced aggregations such as SUM and AVG can be applied with meaningful results.

    For example, assume the data for the aircraft company had the charge for each type of service job expressed as a charge per day.

     

    ID

    Job_Type

    ChargePerDay

    Duration

    123

    Wing

    20

    4 Jan 2011 – 8 Jan 2011

    123

    Fuselage

    10

    5 Jan 2011 – 7 Jan 2011

    123

    Landing Gear

    2

    6 Jan 2011 – 9 Jan 2011

    The following sequenced aggregate query yields meaningful results.

    SEQUENCED VALIDTIME
    SELECT id, 
           SUM (ChargePerDay) TotalChargePerDay ,
           AVG (ChargePerDay) AvgChargePerDay
    FROM aircraft_service
    GROUP BY 1
    ORDER BY VALIDTIME;
     

    ID

    TotalChargePerDay

    AveChargePerDay

    VALIDTIME

    123

    20

    20

    ('11/01/04', '11/01/05')

    123

    30

    15

    ('11/01/05', '11/01/06')

    123

    32

    11

    ('11/01/06', '11/01/07')

    123

    22

    11

    ('11/01/07', '11/01/08')

    123

    2

    2

    ('11/01/08', '11/01/09')

    Example  

    Sequenced aggregations together with GROUP BY in the query includes “empty” valid-time periods during which none of the temporal table rows is valid. Assume the temporal table from the example above included another row for which the valid-time period was not contiguous with any of the existing valid time periods.

     

    ID

    Job_Type

    ChargePerDay

    Duration

    123

    Cockpit

    40

    1 Jan 2012 – 1 Mar 2012

    The query above would have returned two additional rows, one for the new duration, and one for the period that included no aircraft maintenance work:

     

    ID

    TotalChargePerDay

    AvgChargePerDay

    VALIDTIME

    123

    ? (NULL)

    ? (NULL)

    ('11/01/09', '12/01/01')

    123

    40

    40

    ('12/01/01', '12/03/01')

    This allows you to answer questions such as “During the time covered by the table, when were no aircraft undergoing service?”

    SEQUENCED VALIDTIME PERIOD(date'2011-01-01', date'2012-03-01')
    SELECT id FROM aircraft_service
           HAVING COUNT(ChargePerDay)= 0
           GROUP BY 1
           ORDER BY 1;
     

    ID

    VALIDTIME

    123

    ('11/01/09', '12/01/01')

    To prevent the “empty” valid-time period from being returned, add a condition to the query using the HAVING clause to filter out these rows.

    Nonsequenced Valid-Time Queries

    A nonsequenced query operates on all the valid-time states of the underlying table (history, current, future) simultaneously. Such a query is very powerful because it can link across states.

    Use a nonsequenced valid-time query to ignore the time-varying nature of a table or when the computation of a single state of the result table utilizes the information from a state at a different time.

    A nonsequenced query treats the valid-time column as if it is simply a regular column that contains a period value. The query can specify this column anywhere in the query, just as any column.

    A nonsequenced query can select rows with NULL in the valid-time column. None of the other temporal SELECT qualifiers will select such rows.

    If the NONSEQUENCED VALIDTIME qualifier does not specify period_expression, the nonsequenced query on a valid-time table results in a non-valid-time table.

    If period_expression is specified, the result of the query is a valid-time table. However, the valid-time column of the results table is not the same column as the valid-time column in the queried table. The results table includes an additional column named VALIDTIME, that serves as the valid-time column. The value of VALIDTIME in each row is the period of applicability that was specified in the query. This form of a nonsequenced valid-time query can be used to convert a nontemporal table to a table with valid time. Such a query is not permitted in a request that specifies a SET operator (UNION, INTERSECT and MINUS).

    If the projection list is *, the valid-time column is projected with all other non-valid-time columns.

    A reference to VALIDTIME in the same select block in other clauses such as WHERE condition follows the existing resolution rules, and cannot reference the system-projected column. For example, if none of the referenced tables has a column named VALIDTIME an error results. No column in the projection list can use VALIDTIME as an alias.

    If an alias name is specified as the period of applicability, the alias name must be in the scope of the select (projection) list and unambiguously referenced in the projection list.

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

  • “Current Transaction-Time Queries” on page 155
  • “As Of date_time_expression in Transaction-Time Queries” on page 156
  • “Nonsequenced Transaction-Time Queries” on page 157
  • Current Transaction-Time Queries

    A query that specifies CURRENT TRANSACTIONTIME or a query that omits a temporal qualifier and the session does not set a session temporal qualifier is a current query in the transaction-time dimension if the query references a temporal table with transaction time.

    A current query on a temporal table with transaction time considers only those rows in the table that are open in the transaction-time dimension. The result is a snapshot table without transaction time.

    References to a transaction-time column can appear anywhere in the query that references to a non-transaction-time column can appear. A transaction-time column is treated as a conventional Period column on the selected current rows whenever it is used. Using this column in conditions can further filter the output over the current rows.

    A projection list of * indicates all nontemporal columns only.

    A current transaction-time query can join a table with transaction time to a table without transaction time.

    The following rules apply to current transaction-time queries on transaction-time tables:

  • Current query processing is as follows:
  • a Extract the current rows of each of the transaction-time tables specified in the query and treat the query as if the current snapshot equivalent table is specified in the query. The resulting table, regardless of whether the projection list includes the transaction-time column, is a nontemporal table without the transaction-time dimension.

    b Execute the query as if it were a conventional query that was issued on nontemporal tables.

    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:

  • “Current Valid-Time Queries” on page 144
  • “As Of date_time_expression in Valid-Time Queries” on page 146
  • “Sequenced Valid-Time Queries” on page 147
  • “Nonsequenced Valid-Time Queries” on page 154
  • As Of date_time_expression in Transaction-Time Queries

    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

    Teradata Database 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:

  • “Current Valid-Time Queries” on page 144
  • “As Of date_time_expression in Valid-Time Queries” on page 146
  • “Sequenced Valid-Time Queries” on page 147
  • “Nonsequenced Valid-Time Queries” on page 154
  • Nonsequenced Transaction-Time Queries

    Nonsequenced transaction-time queries on transaction-time tables produce nontemporal tables as a result. Use the NONSEQUENCED TRANSACTIONTIME qualifier to query and compare across all transaction-time states simultaneously.

    The nonsequenced query treats a transaction-time table as a table with a regular Period column with no special temporal semantics. References to the transaction-time column can appear anywhere in the query.

    If the projection list specifies *, the transaction-time column is also projected.

    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:

  • “Current Valid-Time Queries” on page 144
  • “As Of date_time_expression in Valid-Time Queries” on page 146
  • “Sequenced Valid-Time Queries” on page 147
  • “Nonsequenced Valid-Time Queries” on page 154
  • Derived Period Columns and SELECT

    In addition to the regular rules for use of derived period columns with SELECT described in SQL Data Manipulation Language, the following rules and restrictions apply to the use of derived VALIDTIME and TRANSACTIONTIME columns in temporal tables.

  • Component columns of derived VALIDTIME and TRANSACTIONTIME columns are not projected for CURRENT and SEQUENCED SELECT * requests.
  • Component columns of derived VALIDTIME and TRANSACTIONTIME columns are projected for NONSEQUENCED SELECT * requests.
  • The system generated VALIDTIME column that is projected for a SEQUENCED VALIDTIME query will be a true period data type column, even if the valid-time column of the queried table is a derived period column.
  • Temporal Queries in Set Operations

    When temporal tables are referenced in queries involving set operations (UNION, INTERSECT, MINUS, and EXCEPT) all queries inherit the temporal qualifier from the topmost query.

    If temporal qualifications are required at the level of individual queries, add them to the FROM clause or place the query with the required qualification in a derived table.

    Example  

    The following query would yield an error:

    VALIDTIME AS OF DATE '2009-05-06' 
    SELECT *
    FROM v1

    MINUS

    VALIDTIME AS OF DATE '2009-05-05'
    SELECT *
    FROM v1

    ORDER BY 1;

    The following query would run properly to yield the desired results:

    SELECT * 
    FROM v1 VALIDTIME AS OF DATE '2009-05-06'

    MINUS

    SELECT *
    FROM v1 VALIDTIME AS OF DATE '2009-05-05'

    ORDER BY 1;

    EXPLAIN Request Modifier

    You can use the EXPLAIN, STATIC EXPLAIN, and DYNAMIC EXPLAIN request modifiers to report temporal semantic operations. The explain text provides the type of qualifier applied on the temporal tables being operated upon. It reports whether the query is current, sequenced, or nonsequenced.

    Here is an example of the EXPLAIN report for a current query on a bitemporal, PPI table:

    EXPLAIN SELECT * FROM Policy;
     
    Explanation
    -----------------------------------------------------------------------
      1) First, we lock a distinct DBASE."pseudo table" for read on a
         RowHash to prevent global deadlock for DBASE.Policy.
      2) Next, we lock DBASE.Policy for read.
      3) We do an all-AMPs RETRIEVE step from a single partition of
         DBASE.Policy (with temporal qualifier as "CURRENT
         VALIDTIME AND CURRENT TRANSACTIONTIME") with a condition of (
         "((BEGIN(DBASE.Policy.Validity ))<= DATE '2010-02-18')
         AND (((END(DBASE.Policy.Policy_Duration ))= TIMESTAMP
         '9999-12-31 23:59:59.999999+00:00') AND
         ((END(DBASE.Policy.Validity ))> DATE '2010-02-18'))")
         into Spool 1 (group_amps), which is built locally on the AMPs.
         The size of Spool 1 is estimated with no confidence to be 1 row (
         85 bytes).  The estimated time for this step is 0.03 seconds.
      4) Finally, we send out an END TRANSACTION step to all AMPs involved
         in processing the request.
      -> The contents of Spool 1 are sent back to the user as the result of
         statement 1.  The total estimated time is 0.03 seconds.

    Time Series Expansion Support

    Time series expansion is indicated using the EXPAND ON clause for SELECT statements, and provides the ability to create a regular time series of rows based on period values in the input rows. The intent is to expand a period column and produce value equivalent rows, one for each granule in a given period of interest.

    For example, suppose an application uses PERIOD(DATE) values to record inventory data of slowly moving items. A SELECT statement can use the EXPAND ON clause to expand the period values by one-week intervals to get the moving average by week of the inventory cost for a specified period of interest.

    To expand the system-generated VALIDTIME column that is automatically appended to the result set of a SEQUENCED VALIDTIME query, the EXPAND ON clause can include the VALIDTIME column name in the expand expression.

    SELECT on Normalized Tables

    The NORMALIZE keyword can be used optionally in SELECT statements to have the results set normalized. The NORMALIZE option of SELECT can be used on tables that do or do not have NORMALIZE in their table definitions.

    The following considerations apply to the use of NORMALIZE with SELECT requests on temporal tables:

  • At least one column in the select list must be a period column or derived period column.
  • The first period column in the select list is the column that is normalized.
  • When a SEQUENCED VALIDTIME SELECT uses NORMALIZE, and no period column is projected in the select list, normalization happens on the system projected “VALIDTIME” column.
  • Normalization cannot be performed using a SELECT statement on a derived period column. As a work around, a true period column can be constructed from the column components of the derived period column.
  • Note that normalization is rarely effective for sequenced select statements on temporal tables that include transaction-time columns, because most of the values in the transaction-time column are likely to be unique, preventing normalization.
  • NORMALIZE can be specified in subqueries.
  • Related Information

     

    For more information on...

    See...

    SELECT statement, including information on the NORMALIZE option

    SQL Data Manipulation Language

    SELECT ... INTO statement

    SQL Stored Procedures and Embedded SQL

    EXPAND ON clause

    SQL Data Manipulation Language

    querying temporal tables

    “Querying Temporal Tables” on page 197

    types of temporal table queries

    “Temporal Table Queries” on page 25