16.20 - System Virtual Columns Returned by Time Series Aggregates - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantageā„¢ Time Series Tables and Operations

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Release Date
March 2019
Content Type
Programming Reference
Publication ID
B035-1208-162K
Language
English (United States)

When a GROUP BY TIME query is executed, two system virtual columns are generated and are accessible to users: $TD_TIMECODE_RANGE and $TD_GROUP_BY_TIME.

$TD_GROUP_BY_TIME and $TD_TIMECODE_RANGE exist only in the presence of a GROUP BY TIME clause that is in the same scope as the virtual columns. A time series supported aggregate function must be used whenever virtual columns are used. However, these system virtual columns cannot be used as arguments to time series supported aggregate functions.

Virtual columns can be used in the SELECT and ORDER BY clauses. Within those clauses, virtual columns may be used in any expression including passed into UDFs and in cast expressions (such that they can change the format associated with embedded timestamps). However, virtual columns cannot be used with WHERE, HAVING, or GROUP BY TIME clauses. Virtual columns cannot be column names in a time series table.

When GROUP BY TIME is used with unbounded time (such as, GROUP BY TIME(*) ), the following rules apply to the system virtual columns:

  • $TD_GROUP_BY_TIME: This always has a value of 1, since there is only one timebucket
  • $TD_TIMECODE_RANGE: This is composed of the first and last timecode values read for the group.