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