The Group By Time SQL element can be dragged or selected from the Other—Expert Options category in the SQL Elements tree. To activate it, the Group By Time element should be dropped on the empty node beneath the Group By node, which will trigger the display of the Group By Time Properties display, as shown below.
Timebucket Duration — I a scheme for dividing time into discrete amounts which can be used, along with the timezero date, to distribute the data across the system. A duration is combined with a positive integer size to define a “timebucket”, such as HOURS(2).
- * (unbounded)
The unbounded timebucket duration, indicated by an asterisk ‘*’, cannot be used with any aggregate function except the DELTA_T aggregate function, and there can be at most one DELTA_T aggregate function used with unbounded time in a select list.
- Size — The positive integer number of time duration units defining a “timebucket".
- Additional Index Columns List — When specified, a timebucket duration effectively defines an index into a time series table. Additional columns may be specified to further define an index. If a column is dragged from the column selector and dropped onto this field, it will be added to any existing column names already present, along with double-quoting each column name. Note that in some cases it may be necessary to manually precede the column name with a table name separated by a period.
- Using Timecode Column — The name of the timecode column, such as TD_TIMECODE. If a column is dragged from the column selector and dropped onto this field, it will replace anything already in this field, since only one column is allowed.
- Sequence Number Column — The name of a sequencing column, such as TD_SEQNO for a time series table, or a column that otherwise plays the role of TD_SEQNO. (A sequencing column makes it possible to have multiple observations for the same timecode value.) If a column is dragged from the column selector and dropped onto this field, it will replace anything already in this field, since only one column is allowed.
Fill Nulls With — A scheme for handling null values, either NULLS, PREVIOUS or NEXT if specified, or blank if not used.
- NULLS — Null timebucket values are replaced with Nulls.
- PREVIOUS — Null timebucket values are replaced with the nearest preceding non-null value
- NEXT — Null timebucket values are replaced with the nearest succeeding non-null value.
- Numeric Constant — In case the Fill Nulls With field is left blank, the Numeric Constant field can be used to specify a numeric value to replace null timebucket values with.