16.20 - TD_GETTIMEBUCKET - 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_GETTIMEBUCKET system function retrieves the TD_TIMEBUCKET column value from a PTI table. Because a timebucket is a hash key, a DBA can use the TD_TIMEBUCKET column value to determine how well the rows of a PTI table are being distributed across AMPs, avoiding skew.

Syntax



Syntax Elements

TD_SYSFNLIB
The name of the database where the function is located.
table_name
The name of the PTI table.
TD_TIMECODE
The PTI table column indicating when the data was collected.

Return Value

The return type of this function is BIGINT.

Usage Notes

The TD_TIMEBUCKET column cannot be directly referenced in an SQL request, so you can use this function to see the TD_TIMEBUCKET value. This function can be referenced in any SQL request where a system function call is valid.

If an SQL request references more than one PTI table, use table_name.TD_TIMECODE to specify the table from which the TD_TIMEBUCKET is being retrieved.

Example: Get a TD_TIMEBUCKET Value from a TD_TIMEBUCKET Column

create table ocean_buoys(buoyid integer, salinity integer, temperature integer)
PRIMARY TIME INDEX(TIMESTAMP(6), DATE '2016-04-19', HOURS(1));
 
-- The following two adjcent SELECT statements should return the same output:
SELECT td_gettimebucket(td_timecode) from ocean_buoys;
SELECT td_gettimebucket(ocean_buoys.td_timecode) from ocean_buoys;
 
TD_GETTIMEBUCKET(TD_TIMECODE)
-----------------------------
289
  
 
-- The column title contains TIMEBUCKET_COL.
SELECT td_gettimebucket(ocean_buoys.td_timecode) as TimeBucket_COL from ocean_buoys;
 
TIMEBUCKET_COL
--------------
           289
 
SELECT buoyid FROM ocean_buoys WHERE td_gettimebucket(td_timecode) > 200;
 
BUOYID
------
   101