GROUP BY TIME Operations | Teradata Vantage - GROUP BY TIME - Time Zero Value - Advanced SQL Engine - Teradata Database

Rule 1: Literal Constants

For example:

/*PTI Table*/

SELECT $TD_TIMECODE_RANGE, $TD_GROUP_BY_TIME, BUOYID, AVG(TEMPERATURE), COUNT(*) FROM OCEAN_BUOYS

WHERE TD_TIMECODE BETWEEN ADD_MONTHS(TIMESTAMP '2013-12-06 08:00:00', 1) AND TIMESTAMP '2014-01-06 10:30:00'

GROUP BY TIME (MINUTES(10) AND BUOYID)

ORDER BY 2, 3;

Result:

Rule 2: Literals Must Be a Compatible Data Type

The timecode literals must be of a data type that can be converted to the data type of the timecode field either the inferred TD_TIMECODE or the explicit column specified in the USING TIMECODE clause). The previous example (in Rule 1: Literal Constants) shows this.

Rule 3: Predicates Must Be Translatable into TIMECODE Ranges

SELECT $TD_TIMECODE_RANGE, $TD_GROUP_BY_TIME, BUOYID, AVG(TEMPERATURE), COUNT(*) FROM OCEAN_BUOYS

WHERE TD_TIMECODE BETWEEN TIMESTAMP '2014-01-06 08:00:00' AND TIMESTAMP '2014-01-06 08:30:00' OR

TD_TIMECODE BETWEEN TIMESTAMP '2014-01-06 10:00:00' AND TIMESTAMP '2014-01-06 10:30:00'

GROUP BY TIME (MINUTES(10) AND BUOYID)

ORDER BY 2, 3;

 *** Query completed. 4 rows found. 5 columns returned.

 *** Total elapsed time was 1 second.

Result:

Rule 4: Starting Point is Negative Infinity and Ending Point is a Literal

SELECT $TD_TIMECODE_RANGE, $TD_GROUP_BY_TIME, BUOYID, AVG(TEMPERATURE), COUNT(*)
FROM OCEAN_BUOYS
WHERE TD_TIMECODE <=  TIMESTAMP '2014-01-06 09:00:00'
GROUP BY TIME (MINUTES(10) AND BUOYID)
ORDER BY 2, 3;

Result:

Rule 5: The Starting Point is a Literal and the End Point is Positive Infinity

SELECT $TD_TIMECODE_RANGE, $TD_GROUP_BY_TIME, BUOYID, AVG(TEMPERATURE), COUNT(*)
FROM OCEAN_BUOYS
WHERE TD_TIMECODE >=  TIMESTAMP '2014-01-06 08:00:00'
GROUP BY TIME (MINUTES(10) AND BUOYID)
ORDER BY 2, 3;

Result:

Rule 6: Starting Point of Negative Infinity and Ending Point of Positive Infinity

SELECT $TD_TIMECODE_RANGE, $TD_GROUP_BY_TIME, BUOYID, AVG(TEMPERATURE), COUNT(*)
FROM OCEAN_BUOYS
GROUP BY TIME (MINUTES(10) AND BUOYID)
ORDER BY 2, 3;

Result: