Purpose
The GROUP BY TIME clause is an enhancement to the SELECT statement that allows a set of aggregate functions to be computed on data grouped in terms of time. Although the grouping is optimized for PTI tables, it is also supported on non-PTI tables because a timecode column is explicitly specified in the USING TIMECODE clause.
Syntax
Syntax Elements
- GROUP BY TIME
- The following describes the syntax for the GROUP BY TIME clause.
- timebucket duration
- A time duration that can be specified using any of the units of time shown in the diagram. Abbreviations are allowed for the duration:
Time Unit Formal Form Example Shorthand Equivalents Calendar Years CAL_YEARS(4) 4cy 4cyear
4cyearsCalendar Months CAL_MONTHS(5) 5cm 5cmonth
5cmonthsCalendar Days 24 hour periods starting at 00:00:00.000000 and ending at 23:59:59.999999 on the day identified by time zero.
CAL_DAYS(6) 6cd 6cday
6cdaysWeeks WEEKS(3) 3w 3week
3weeksDays 24 hour periods starting from time zero.
DAYS(5) 5d 5day
5daysHours HOURS(4) 4h 4hr
4hrs
4hour
4hoursMinutes MINUTES(23) 23m 23mins
23minute
23minutesSeconds SECONDS(33) 33s 33sec
33secs
33second
33secondsMilliseconds MILLISECONDS(12) 12ms 12msec
12msecs
12millisecond
12millisecondsMicroseconds MICROSECONDS(10) 10us 10usec
10usecs
10microsecond
10microseconds - pos_int
- A 16-bit positive integer with a maximum value of 32767.
- value_expression
-
The value_expression is a column or any expression involving columns (except for scalar subqueries). These expressions are used for grouping purposes not related to time.
There can be one or more comma separated value expressions.
The value_expression must not be a column reference to a view column that is derived from a function and cannot contain any ordered analytical or aggregate functions. The value_expression cannot be a literal.
- pos_int
- A positive integer in the range of 1 to 32767 inclusively.
- optional_clauses
- The optional clauses for GROUP BY TIME are: USING TIMECODE and FILL.
- timestamp_date_col
- A column expression (with an optional table name) that serves as the timecode for a non-PTI table.
- seqno_col
- A column expression (with an optional table name) that is the sequence number. For a PTI table, it can be TD_SEQNO or any other column that acts as a sequence number. For a non-PTI table, seqno_col is a column that plays the role of TD_SEQNO (because non-PTI tables do not have TD_SEQNO).
- fill
- The FILL clause allows you to provide values for missing timebucket values. The following variables and values are used by the FILL clause. For more information, see Using the FILL Clause to Handle Missing Timebuckets.
- NULLS
- The missing timebuckets are returned to the user with a null value for all aggregate results.
- numeric_constant
- Any Teradata Database supported Numeric literal. The missing timebuckets are returned to the user with the specified constant value for all aggregate results. If the data type specified in the FILL clause is incompatible with the input data type for an aggregate function, an error is reported.
- PREVIOUS
- PREV
- The missing timebuckets are returned to the user with the aggregate results populated by the value of the closest previous timebucket with a non-missing value. If the immediate predecessor of a missing timebucket is also missing, both buckets, and any other immediate predecessors with missing values, are loaded with the first preceding non-missing value. If a missing timebucket has no predecessor with a result (for example, if the timebucket is the first in the series or all the preceding timebuckets in the entire series are missing), the missing timebuckets are returned to the user with a null value for all aggregate results. The abbreviation PREV may be used instead of PREVIOUS.
- NEXT
- The missing timebuckets are returned to the user with the aggregate results populated by the value of the closest succeeding timebucket with a non-missing value. If the immediate successor of a missing timebucket is also missing, both buckets, and any other immediate successors with missing values, are loaded with the first succeeding non-missing value. If a missing timebucket has no successor with a result (for example, if the timebucket is the last in the series or all the succeeding timebuckets in the entire series are missing), the missing timebuckets are returned to the user with a null value for all aggregate results.