GROUP BY TIME Clause Syntax | Teradata Vantage - GROUP BY TIME Clause Syntax - Advanced SQL Engine - Teradata Database

Time Series Tables and Operations

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-28
dita:mapPath
zzg1600277315070.ditamap
dita:ditavalPath
zzg1600277315070.ditaval
dita:id
B035-1208
lifecycle
previous
Product Category
Teradata Vantage™
{ GROUP BY options |

  GROUP BY TIME ( { timebucket_duration | * }
    [ AND value_expression [,...] ] )
    [ USING ( timestamp_date_col [, seqno_col ] ) ]
    [ FILL ( { NULLS | numeric_constant | PREVIOUS | NEXT } ) ] 
}

Syntax Elements

options
The optional clauses for GROUP BY TIME are: USING TIMECODE and FILL.
timebucket_duration
{ CAL_YEARS |
  CAL_MONTHS |
  CAL_DAYS |
  WEEKS |
  DAYS |
  HOURS |
  MINUTES |
  SECONDS |
  MILLISECONDS |
  MICROSECONDS
} ( pos_int )
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

4cyears
Calendar Months CAL_MONTHS(5) 5cm

5cmonth

5cmonths
Calendar 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

6cdays
Weeks WEEKS(3) 3w

3week

3weeks
Days

24 hour periods starting from time zero.

DAYS(5) 5d

5day

5days
Hours HOURS(4) 4h

4hr

4hrs

4hour

4hours
Minutes MINUTES(23) 23m

23mins

23minute

23minutes
Seconds SECONDS(33) 33s

33sec

33secs

33second

33seconds
Milliseconds MILLISECONDS(12) 12ms

12msec

12msecs

12millisecond

12milliseconds
Microseconds MICROSECONDS(10) 10us

10usec

10usecs

10microsecond

10microseconds

The time unit representation (CAL_YEARS, CAL_MONTHS, and so on) is used to specify the timebucket_duration for both the CREATE TABLE (Time Series Form) and the GROUP BY TIME clause.

When the time unit is used within a GROUP BY TIME clause, it defines the duration of each timebucket for aggregation and is used to assign each potential timebucket a unique number. You can access the timebucket number in the $TD_GROUP_BY_TIME virtual column. For more information, see $TD_GROUP_BY_TIME.

The time units do not store values such as the year or the month. For example, CAL_YEARS(2017) does not set the year to 2017. It sets the timebucket_duration to intervals of 2017 years. Similarly, CAL_MONTHS(7) does not set the month to July. It sets the timebucket_duration to intervals of 7 months.

A calendar day (CAL_DAYS) is a 24 hour period starting at 00:00:00.000000 and ending at 23:59:59.999999; for example, Friday from 00:00:00.000000 to 23:59:59.999999.

A Days time unit is a 24 hour span relative to any moment in time. For example, in a GROUP BY TIME query with time zero equal to 2016-10-01 12:00:00, the day buckets are 2016-10-01 12:00:00.000000 – 2016-10-02 11:59:59.999999. This spans multiple calendar days, but encompasses one 24 hour period representative of a day.

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.

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 Vantage 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.
pos_int
A 16-bit positive integer with a maximum value of 32767.
A positive integer in the range of 1 to 32767 inclusively.