GROUP BY TIME Operations | Teradata Vantage - 17.00 - GROUP BY TIME - Time Zero Value - Teradata Database

Teradata Vantage™ - Time Series Tables and Operations

prodname
Advanced SQL Engine
Teradata Database
vrm_release
17.00
created_date
June 2020
category
Programming Reference
featnum
B035-1208-170K

Every GROUP BY TIME operation has a time zero value associated with it.

If the time series query has just one associated time range, then the time zero value is equal to the starting time associated with the range. For example, in a statement such as,
SELECT … WHERE TD_TIMECODE BETWEEN a and b
a is the time zero value.

If the time series query has multiple time ranges specified (with intervening ORs), then the time zero value is the value corresponding to the oldest starting time among the time ranges specified in the WHERE clause. For example, in a statement such as:

SELECT ... WHERE TD_TIMECODE BETWEEN a AND b OR TIMECODE BETWEEN c AND d OR
TIMECODE BETWEEN e AND f

The starting times from each range (in this case, a, c, and e) are compared and the one with the earliest time is the time zero value.

If the time series query has no specified time range, the GROUP BY TIME operation is applied to the entire time scope of the series. The time zero value is set to the time zero value associated with the PTI table against which this time series query is being issued or if no PTI tables are present time zero is set to the UNIX epoch time (1970-01-01 00:00:00).

If the USING TIMECODE clause is specified, the column that is used for the time zero value is the column specified in the USING TIMECODE clause.

The following rules explain how time zero is calculated.

Rule 1: Literal Constants

Values for the timecode can only be calculated from literal constants or from an expression that evaluates to a literal constant; for example:
  • Values such as TIMESTAMP ’01-01-31 08:00:00’ can be specified.
  • An expression, such as ADD_MONTHS(TIMESTAMP '2013-12-06 08:00:00’, 1), can be specified if the expression evaluates to a literal.
  • Timecode ranges cannot include any column references.

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:

TIMECODE_RANGE GROUP BY TIME(MINUTES(10)) BUOYID Average(TEMPERATURE) COUNT(*)
('2014-01-06 08:00:00.000000+00:00', '2014-01-06 08:10:00.000000+00:00') 1 0 54 3
('2014-01-06 08:10:00.000000+00:00', '2014-01-06 08:20:00.000000+00:00') 2 0 55 2
('2014-01-06 09:00:00.000000+00:00', '2014-01-06 09:10:00.000000+00:00') 7 1 74 6
('2014-01-06 10:00:00.000000+00:00', '2014-01-06 10:10:00.000000+00:00') 13 44 50 10
('2014-01-06 10:10:00.000000+00:00', '2014-01-06 10:20:00.000000+00:00') 14 44 43 1

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

The predicates against the timecode (either the inferred TD_TIMECODE or the explicit column specified in the USING TIMECODE clause) must be translatable into a series of one or more timecode ranges:
  • A number of ranges may be connected by OR.
  • In assigning the timebucket number (for example, 1, 2, 3), the earliest starting point (the one closest to negative infinity) is treated as time zero. This means that each timebucket after time zero has its timebucket number computed based on this one time zero (not the starting point of a particular range, but the starting point of all of the ranges).
  • All empty timebuckets within specified ranges are filled as specified by the FILL clause. Timebuckets that are excluded based on the ranges specified are not filled.
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:

TIMECODE_RANGE GROUP BY TIME(MINUTES(10)) BUOYID Average(TEMPERATURE) COUNT(*)
('2014-01-06 08:00:00.000000+00:00', '2014-01-06 08:10:00.000000+00:00') 1 0 54 3
('2014-01-06 08:10:00.000000+00:00', '2014-01-06 08:20:00.000000+00:00') 2 0 55 2
('2014-01-06 10:00:00.000000+00:00', '2014-01-06 10:10:00.000000+00:00') 13 44 50 10
('2014-01-06 10:10:00.000000+00:00', '2014-01-06 10:20:00.000000+00:00') 14 44 43 1

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

If the starting point of a range is negative infinity and the endpoint is a literal, the following applies:
  • For the GROUP BY TIME timebucket number assignment:
    • If the source table is a PTI table, the time zero associated with the source table is used to generate the timebucket number.
    • If the source table is not a PTI table, EPOCH time is used to generate the timebucket number.
  • For the FILL clause, each processed series is considered independently:
    • The starting time (the first time relevant to that series) associated with that particular series is the first relevant timebucket.
    • All missing timebuckets found between the first timebucket and the provided end point are filled.
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:

TIMECODE_RANGE GROUP BY TIME(MINUTES(10)) BUOYID Average(TEMPERATURE) COUNT(*)
('2014-01-06 08:00:00.000000+00:00', '2014-01-06 08:10:00.000000+00:00') 106033 0 54 3
('2014-01-06 08:10:00.000000+00:00', '2014-01-06 08:20:00.000000+00:00') 106034 0 55 2

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

If a literal is provided for the starting point of a range and the endpoint is positive infinity, the following applies:
  • For GROUP BY TIME, the provided literal starting point is the time zero value used for the timebucket number assignment.
  • For the FILL clause, each processed series is considered independently:
    • The provided starting time is time zero.
    • Each series has an associated last row which belongs to the GROUP BY TIME timebucket that constitutes its last timebucket.
    • Missing timebuckets between the first timebucket (specified by the starting time) and the ending point timebucket are filled.
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:

TIMECODE_RANGE GROUP BY TIME(MINUTES(10)) BUOYID Average(TEMPERATURE) COUNT(*)
('2014-01-06 08:00:00.000000+00:00', '2014-01-06 08:10:00.000000+00:00') 1 0 54 3
('2014-01-06 08:10:00.000000+00:00', '2014-01-06 08:20:00.000000+00:00') 2 0 55 2
('2014-01-06 09:00:00.000000+00:00', '2014-01-06 09:10:00.000000+00:00') 7 1 74 6
('2014-01-06 10:00:00.000000+00:00', '2014-01-06 10:10:00.000000+00:00') 13 44 50 10
('2014-01-06 10:10:00.000000+00:00', '2014-01-06 10:20:00.000000+00:00') 14 44 43 1
('2014-01-06 10:30:00.000000+00:00', '2014-01-06 10:40:00.000000+00:00') 16 44 43 1
('2014-01-06 10:50:00.000000+00:00', '2014-01-06 11:00:00.000000+00:00') 18 44 43 1
('2014-01-06 21:00:00.000000+00:00', '2014-01-06 21:10:00.000000+00:00') 79 2 81 3

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

If the starting point of a range is negative infinity and the endpoint of a range is positive infinity (for example, SELECT BUOYID, AVG(TEMP) FROM T1 GROUP BY TIME(MINUTES(15) AND BUOYID), the following applies:
  • For the GROUP BY TIME timebucket number assignment:
    • If the source table is a PTI, the time zero associated with the source table is used to generate the timebucket number.
    • If the source table is not a PTI, EPOCH time is used to generate the timebucket number.
  • For the FILL clause, each processed series is considered independently:
    • The starting time (the first row relevant to that series) associated with that particular series is the first relevant timebucket.
    • The ending time (the last row relevant to that series) associated with a particular series is the last relevant timebucket.
    • All missing timebuckets found between the first relevant timebucket and the last relevant timebucket point are filled. This is repeated for each series processed.
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:

TIMECODE_RANGE GROUP BY TIME(MINUTES(10)) BUOYID Average(TEMPERATURE) COUNT(*)
('2014-01-06 08:00:00.000000+00:00', '2014-01-06 08:10:00.000000+00:00') 106033 0 54 3
('2014-01-06 08:10:00.000000+00:00', '2014-01-06 08:20:00.000000+00:00') 106034 0 55 2
('2014-01-06 09:00:00.000000+00:00', '2014-01-06 09:10:00.000000+00:00') 106039 1 74 6
('2014-01-06 10:00:00.000000+00:00', '2014-01-06 10:10:00.000000+00:00') 106045 44 50 10
('2014-01-06 10:10:00.000000+00:00', '2014-01-06 10:20:00.000000+00:00') 106046 44 43 1
('2014-01-06 10:30:00.000000+00:00', '2014-01-06 10:40:00.000000+00:00') 106048 44 43 1
('2014-01-06 10:50:00.000000+00:00', '2014-01-06 11:00:00.000000+00:00') 106050 44 43 1
('2014-01-06 21:00:00.000000+00:00', '2014-01-06 21:10:00.000000+00:00') 106111 2 81 3