GROUP BY TIME Usage Notes | Teradata Vantage - Usage Notes - Advanced SQL Engine - Teradata Database

GROUP BY TIME and GROUP BY cannot be used together in the same query (this restriction includes GROUP BY ROLLUP, GROUP BY CUBE, and so on).

If GROUP BY TIME is used on a non-PTI table, the USING TIMECODE clause must be included; otherwise, an error is reported.

A supported Time Series function must be used in conjunction with a GROUP BY TIME clause; otherwise, an error is reported.

The timebuckets (which serve as the first level of grouping, if specified) are computed based on time zero. For more information about how time zero is calculated, see GROUP BY TIME - Time Zero Value.

Grouping is determined first by timebucket, and then by all other fields specified in the GROUP BY TIME clause (if any). A timebucket duration is required with the GROUP BY TIME clause. Failure to include it results in an error.

The HAVING clause is supported for filtering results of aggregates with the GROUP BY TIME clause.

The QUALIFY and WITH BY clauses are NOT supported when the GROUP BY TIME clause is present.

The USING TIMECODE and FILL clauses are optional and may only be used with a GROUP BY TIME clause.

The timecode column cannot be specified as a value_expression in the GROUP BY TIME clause, whether the timecode column is the system generated TD_TIMECODE column of a PTI table or specified in the USING TIMECODE clause. Similarly, the sequence number column (TD_SEQNO or seqno_col of the USING TIMECODE clause) cannot be specified as a value_expression in the GROUP BY TIME clause.

The PTI tables require the TD_TIMECODE and TD_SEQNO columns to be non NULL. When the USING TIMECODE clause is used to specify a timecode or sequenced number column, rows with NULL values are filtered out for these columns. The following example shows this:

CREATE SET TABLE aggr_table ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO
     (
      TD_TIMECODE TIMESTAMP(6) NOT NULL GENERATED TIMECOLUMN,
      col1 DECIMAL(20,5),
      vc VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,
      ts TIMESTAMP(6))
 PRIMARY TIME INDEX (TIMESTAMP(6), DATE '2000-01-01', COLUMNS(col1), NONSEQUENCED);

SEL $TD_TIMECODE_RANGE, $TD_GROUP_BY_TIME, MODE(col1) 
FROM aggr_table
GROUP BY TIME(CAL_YEARS(100)) 
USING TIMECODE(ts)
order by 1,2,3;

The USING TIMECODE clause causes the query to be rewritten internally to include the following condition on the "ts" column:

SEL $TD_TIMECODE_RANGE, $TD_GROUP_BY_TIME, MODE(col1) 
FROM aggr_table
WHERE ts IS NOT NULL
GROUP BY TIME(CAL_YEARS(100)) 
USING TIMECODE(ts)
order by 1,2,3;

Any conditions added implicitly are chained to any existing conditions using an AND condition.

Time series queries with unbounded time (GROUP BY TIME (*)) can have at most one DELTA_T aggregate specified in the SELECT list. DELTA_T is the only aggregate function that can be used with unbounded time.

Limitations

GROUP BY TIME works only if applied directly on a base PTI table. In all other cases, including joins, USING TIMECODE is used to explicitly specify the timecode column for the times series aggregate. For example:

ct t1(a1 int, b1 int, c1 int) primary index (a1);
insert into t1(0,0,0);
insert into t1(1,1,1);
 
The join spool is just like any other join in Teradata. There is nothing specific to Time Series.

SELECT * FROM OCEAN_BUOYS, t1 WHERE TD_TIMECODE BETWEEN TIMESTAMP '2014-01-06 08:00:00' AND TIMESTAMP '2014-01-06 10:30:00' AND buoyid = t1.b1;
 

Result: