The $TD_GROUP_BY_TIME system virtual column stores the number of the timebucket, such as 1, 2, 3, and so on.
- 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 |