System Virtual Columns | Teradata Vantage - 17.00 - System Virtual Columns Returned by Time Series Aggregates - Teradata Database

Teradata Vantageā„¢ - Time Series Tables and Operations

prodname
Advanced SQL Engine
Teradata Database
vrm_release
17.00
created_date
June 2020
category
Programming Reference
featnum
B035-1208-170K

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.