16.20 - TD_TIME_BUCKET_NUMBER - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantageā„¢ Time Series Tables and Operations

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Release Date
March 2019
Content Type
Programming Reference
Publication ID
B035-1208-162K
Language
English (United States)

Purpose

The TD_TIME_BUCKET_NUMBER system function calculates the time bucket number. You can use this function with the HASHROW, HASHBUCKET, or HASHAMP functions to see how time series rows are distributed across the system. Other uses include:
  • Determining which candidate rows are assigned to which bucket in a GROUP BY TIME operation
  • Using the result in a join or to insert into a table if you assign bucket numbers to rows outside of a GROUP BY TIME operation

Syntax



Syntax Elements

TD_SYSFNLIB
The name of the database where the function is located.
timezero

Any expression evaluating to a DATE or TIMESTAMP that is used as time zero for this function. A time zone for the timestamp is optional.

timecode
Any expression evaluating to a DATE or TIMESTAMP that is used as the timecode for this function. A time zone for the timestamp is optional.
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

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
pos_int
A 16-bit positive integer with a maximum value of 32,767.

Return Value

The return type of this function is BIGINT.

Usage Notes

You can reference this function in any SQL request where a system function call is valid.

Example: Use TD_TIME_BUCKET_NUMBER to Calculate a Time Bucket Number

SELECT
TD_TIMECODE,
TD_TIME_BUCKET_NUMBER(DATE '1900-01-01', TD_TIMECODE, CAL_YEARS(10)) FROM
ts_group_by_time_tb
WHERE id = 0
ORDER BY 2;
                                                       
TD_TIMECODE                    TD_TIME_BUCKET_NUMBER(1900-01-01,TD_TIMECODE,CAL_YEARS(10))                  
2006-06-06 06:06:06.006002    11