$TD_GROUP_BY_TIME | Teradata Vantage - $TD_GROUP_BY_TIME - Advanced SQL Engine - Teradata Database

Time Series Tables and Operations

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-22
dita:mapPath
cxa1555383531762.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1208
lifecycle
previous
Product Category
Teradata Vantage™

The $TD_GROUP_BY_TIME system virtual column stores the number of the timebucket, such as 1, 2, 3, and so on.

To retrieve and use the group by time system virtual column, access $TD_GROUP_BY_TIME in a function or an ORDER BY clause:
  • The default TITLE is GROUP BY TIME
  • You may change the name of the default title via the AS clause
  • The data type of the column is BIGINT
  • Utilize the $TD_GROUP_BY_TIME name label in the same manner as you use BIGINT:
    • You can reference $TD_GROUP_BY_TIME directly
    • You can use $TD_GROUP_BY_TIME in any expression (including passing it to functions)
    • You can use $TD_GROUP_BY_TIME in an ORDER BY clause similar to any other BIGINT column

$TD_GROUP_BY_TIME Values

Starting at the GROUP BY TIME time zero value, the time continuum is broken into a series of equally sized continuous time segments each having a duration equal to the GROUP BY TIME time interval. For example, if the time zero value is 2016-10-01 08:00 and the GROUP BY TIME time interval is 15 minutes, then the time continuum is broken into a series of continuous segments of size 15 minutes, starting at the time zero value.

The numbering scheme for these time segments is to label the first segment number 1 and then do a positive incremental integral assignment thereafter, such as: 1,2,3,4, … Last. This numbering assignment is referred to as the GROUP BY TIME timebucket number.

The following example shows a single time range specified as part of the query:

SELECT … WHERE TD_TIMECODE BETWEEN TIMESTAMP ’2016-10-01 08:00:00’ AND TIMESTAMP ’2016-10-01 09:00:00’ GROUP BY TIME(MINUTES(15))

This produces the following results:

GROUP BY TIME(MINUTES(15))
1
2
3
4

The following example query specifies multiple time ranges:

SELECT … WHERE TD_TIMECODE BETWEEN TIMESTAMP ’2016-10-01 08:00:00’ AND TIMESTAMP ’2016-10-01 09:00:00’
OR TIMECODE BETWEEN TIMESTAMP ’2016-10-01 12:15:00’ AND TIMESTAMP ’2016-10-01 13:30:00’
GROUP BY TIME(MINUTES(15))

Result:

GROUP BY TIME(MINUTES(15))
1
2
3
4
18
19
20
21
22