16.20 - SELECT - 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)

When querying PTI tables, you can use the GROUP BY TIME clause. See GROUP BY TIME Clause. For complete information about SELECT, see Teradata Vantage™ SQL Data Manipulation Language , B035-1146 .

When referencing a PTI table in a SELECT statement:
  • Specifying SELECT * from a PTI table defined with a TD_TIMEBUCKET column returns all of the columns in the table except for the system generated TD_TIMEBUCKET column.
  • When querying PTI tables, you can specify the system generated TD_TIMECODE and TD_SEQNO columns in the SELECT statement.
  • You cannot specify the system generated TD_TIMEBUCKET column in a SELECT statement. However, you can use the TD_GETTIMEBUCKET() function to obtain TD_TIMEBUCKET data. See TD_GETTIMEBUCKET.
  • The BETWEEN logical predicate is inclusive of the lower and upper bounds, which can span multiple time bucket boundaries. If you specify GROUP BY TIME (HOURS(1)), each time bucket only includes the lower bound of the one hour time range. A timestamp of 08:00:00 is in one timebucket and a timestamp of 09:00:00 is in the next time bucket. For example, if you specify BETWEEN 08:00:00 AND 09:00:00 and one hour time buckets are defined, that is,HOURS(1), rows from two time buckets can be returned.
  • As with any SELECT statement, you must specify an order, otherwise rows are returned unordered. For example, SELECT * FROM table_name ORDER BY column_name.

Retrieving Time Series Data from Two Periods

This example is used to retrieve two days of cellphone tower signal strength using union. The output of $TD_GROUP_BY_TIME (timebucket) from each day is used to correlate a same 10-minute interval, which allows the user to compare the tower signal change side by side for each 10-minute interval between the two days.

CREATE TABLE cell_towers_dt_pti (celltwrcell INT, celltwrrange INT, celltwrsignal INT) 
PRIMARY TIME INDEX (TIMESTAMP(0), DATE '1990-01-01', HOURS(1) );

SHOW TABLE cell_towers_dt_pti;

---------------------------------------------------------------------------
CREATE SET TABLE JW.cell_towers_dt_pti ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO,
     MAP = TD_MAP1
     (
      TD_TIMEBUCKET BIGINT NOT NULL GENERATED SYSTEM TIMECOLUMN,
      TD_TIMECODE TIMESTAMP(0) NOT NULL GENERATED TIMECOLUMN,
      celltwrcell INTEGER,
      celltwrrange INTEGER,
      celltwrsignal INTEGER)
PRIMARY TIME INDEX (TIMESTAMP(0), DATE '1990-01-01', HOURS(1), NONSEQUENCED);

SELECT
   $TD_TIMECODE_RANGE AS tcrange,
   $TD_GROUP_BY_TIME AS timebucket
   ,first(celltwrcell) AS iotFirst
   ,median(celltwrrange) AS iotTypical
  FROM
    cell_towers_dt_pti
WHERE
   TD_TIMECODE BETWEEN TIMESTAMP '2015-01-01 00:00:00' AND TIMESTAMP
'2015-01-01 23:59:59'
GROUP BY
   TIME(minutes(10))
UNION
SELECT
   $TD_TIMECODE_RANGE AS tcrange2,
   $TD_GROUP_BY_TIME AS timebucket2
   ,first(celltwrcell) AS iotFirst2
   ,median(celltwrrange) as iotTypical2
  FROM
cell_towers_dt_pti
WHERE
   TD_TIMECODE BETWEEN TIMESTAMP '2015-01-02 00:00:00' AND TIMESTAMP
'2015-01-02 23:59:59'
GROUP BY
   TIME(minutes(10))
ORDER BY 2,1
;

*** Query completed. No rows found.
*** Total elapsed time was 1 second.