Table and Data Definition Example | Teradata Vantage - Table and Data Definition for Time Series Aggregates Examples - Analytics Database - Teradata Vantage

Time Series Tables and Operations
Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-10-30
dita:mapPath
tuc1628112453431.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
sfz1493079039055
lifecycle
latest
Product Category
Teradata Vantage™

The following SQL creates sample tables with data for use in time series examples.

/*PTI Table*/
CREATE TABLE OCEAN_BUOYS(BUOYID INTEGER, SALINITY INTEGER, TEMPERATURE INTEGER ) 

PRIMARY TIME INDEX (TIMESTAMP(6), DATE '2012-01-01', HOURS(1), COLUMNS(BUOYID), nonsequenced);

INSERT INTO OCEAN_BUOYS VALUES(TIMESTAMP '2014-01-06 08:00:00.000000', 0, 55, 10);
INSERT INTO OCEAN_BUOYS VALUES(TIMESTAMP '2014-01-06 08:08:59.999999', 0, 55, );
INSERT INTO OCEAN_BUOYS VALUES(TIMESTAMP '2014-01-06 08:09:59.999999', 0, 55, 99);
INSERT INTO OCEAN_BUOYS VALUES(TIMESTAMP '2014-01-06 08:10:00.000000', 0, 55, 10);
INSERT INTO OCEAN_BUOYS VALUES(TIMESTAMP '2014-01-06 08:10:00.000000', 0, 55, 100);
INSERT INTO OCEAN_BUOYS VALUES(TIMESTAMP '2014-01-06 09:01:25.122200', 1, 55, 77);
INSERT INTO OCEAN_BUOYS VALUES(TIMESTAMP '2014-01-06 09:01:25.122200', 1, 55, 70);
INSERT INTO OCEAN_BUOYS VALUES(TIMESTAMP '2014-01-06 09:02:25.122200', 1, 55, 78);
INSERT INTO OCEAN_BUOYS VALUES(TIMESTAMP '2014-01-06 09:02:25.122200', 1, 55, 71);
INSERT INTO OCEAN_BUOYS VALUES(TIMESTAMP '2014-01-06 09:03:25.122200', 1, 55, 79);
INSERT INTO OCEAN_BUOYS VALUES(TIMESTAMP '2014-01-06 09:03:25.122200', 1, 55, 72);
INSERT INTO OCEAN_BUOYS VALUES(TIMESTAMP '2014-01-06 21:01:25.122200', 2, 55, 80);
INSERT INTO OCEAN_BUOYS VALUES(TIMESTAMP '2014-01-06 21:02:25.122200', 2, 55, 81);
INSERT INTO OCEAN_BUOYS VALUES(TIMESTAMP '2014-01-06 21:03:25.122200', 2, 55, 82);
INSERT INTO OCEAN_BUOYS VALUES(TIMESTAMP '2014-01-06 10:00:24.000000', 44, 55, 43);
INSERT INTO OCEAN_BUOYS VALUES(TIMESTAMP '2014-01-06 10:00:24.333300', 44, 55, 43);
INSERT INTO OCEAN_BUOYS VALUES(TIMESTAMP '2014-01-06 10:00:25.122200', 44, 55, 43);
INSERT INTO OCEAN_BUOYS VALUES(TIMESTAMP '2014-01-06 10:00:26.122200', 44, 55, 43);
INSERT INTO OCEAN_BUOYS VALUES(TIMESTAMP '2014-01-06 10:01:25.122200', 44, 55, 53);
INSERT INTO OCEAN_BUOYS VALUES(TIMESTAMP '2014-01-06 10:01:25.122200', 44, 55, 54);
INSERT INTO OCEAN_BUOYS VALUES(TIMESTAMP '2014-01-06 10:02:25.122200', 44, 55, 53);
INSERT INTO OCEAN_BUOYS VALUES(TIMESTAMP '2014-01-06 10:02:25.122200', 44, 55, 55);
INSERT INTO OCEAN_BUOYS VALUES(TIMESTAMP '2014-01-06 10:03:25.122200', 44, 55, 53);
INSERT INTO OCEAN_BUOYS VALUES(TIMESTAMP '2014-01-06 10:03:25.122200', 44, 55, 56);
INSERT INTO OCEAN_BUOYS VALUES(TIMESTAMP '2014-01-06 10:12:00.000000', 44, 55, 43);
INSERT INTO OCEAN_BUOYS VALUES(TIMESTAMP '2014-01-06 10:32:12.122200', 44, 55, 43);
INSERT INTO OCEAN_BUOYS VALUES(TIMESTAMP '2014-01-06 10:52:00.000009', 44, 55, 43);

/* Sequenced PTI table */
CREATE TABLE OCEAN_BUOYS_SEQ(BUOYID INTEGER, SALINITY INTEGER, TEMPERATURE INTEGER, DATES DATE)

PRIMARY TIME INDEX (TIMESTAMP(6), DATE '2012-01-01', HOURS(1), COLUMNS(BUOYID), sequenced);

INSERT INTO OCEAN_BUOYS_SEQ VALUES(TIMESTAMP '2014-01-06 10:32:12.122200',1, 44, 55, 43, '2014-01-01');
INSERT INTO OCEAN_BUOYS_SEQ VALUES(TIMESTAMP '2014-01-06 10:32:12.122200',1, 22, 25, 23, '2014-01-01');
INSERT INTO OCEAN_BUOYS_SEQ VALUES(TIMESTAMP '2014-01-06 10:52:00.000009',2, 44, 55, 43, '2014-02-02');
INSERT INTO OCEAN_BUOYS_SEQ VALUES(TIMESTAMP '2014-01-06 10:12:00.000000',3, 44, 55, 43, '2014-03-03');
INSERT INTO OCEAN_BUOYS_SEQ VALUES(TIMESTAMP '2014-01-06 10:00:24.000000',4, 44, 55, 43, '2014-04-04');
INSERT INTO OCEAN_BUOYS_SEQ VALUES(TIMESTAMP '2014-01-06 10:00:24.333300',5, 44, 55, 43, '2014-05-05');
INSERT INTO OCEAN_BUOYS_SEQ VALUES(TIMESTAMP '2014-01-06 10:00:25.122200',6, 44, 55, 43, '2014-06-06');
INSERT INTO OCEAN_BUOYS_SEQ VALUES(TIMESTAMP '2014-01-06 10:00:26.122200',7, 44, 55, 43, '2014-07-07');
INSERT INTO OCEAN_BUOYS_SEQ VALUES(TIMESTAMP '2014-01-06 10:01:25.122200',8, 44, 55, 53, '2014-08-08');
INSERT INTO OCEAN_BUOYS_SEQ VALUES(TIMESTAMP '2014-01-06 10:02:25.122200',9, 44, 55, 53, '2014-09-09');
INSERT INTO OCEAN_BUOYS_SEQ VALUES(TIMESTAMP '2014-01-06 10:03:25.122200',10, 44, 55, 53, '2014-10-10');
INSERT INTO OCEAN_BUOYS_SEQ VALUES(TIMESTAMP '2014-01-06 09:01:25.122200',11, 1, 55, 70,  '2014-11-11');
INSERT INTO OCEAN_BUOYS_SEQ VALUES(TIMESTAMP '2014-01-06 09:02:25.122200',12, 1, 55, 71,  '2014-12-12');
INSERT INTO OCEAN_BUOYS_SEQ VALUES(TIMESTAMP '2014-01-06 09:03:25.122200',13, 1, 55, 72,  '2015-01-13');
INSERT INTO OCEAN_BUOYS_SEQ VALUES(TIMESTAMP '2014-01-06 21:01:25.122200',14, 2, 55, 80,  '2015-02-14');
INSERT INTO OCEAN_BUOYS_SEQ VALUES(TIMESTAMP '2014-01-06 21:02:25.122200',15, 2, 55, 81,  '2015-03-15');
INSERT INTO OCEAN_BUOYS_SEQ VALUES(TIMESTAMP '2014-01-06 21:03:25.122200',16, 2, 55, 82,  '2015-04-16');
INSERT INTO OCEAN_BUOYS_SEQ VALUES(TIMESTAMP '2014-01-06 08:09:59.999999',17, 0, 55, 99,  '2015-05-17');
INSERT INTO OCEAN_BUOYS_SEQ VALUES(TIMESTAMP '2014-01-06 08:08:59.999999',18, 0, 55, ,    '2015-06-18');
INSERT INTO OCEAN_BUOYS_SEQ VALUES(TIMESTAMP '2014-01-06 08:10:00.000000',19, 0, 55, 10,  '2015-07-19');
INSERT INTO OCEAN_BUOYS_SEQ VALUES(TIMESTAMP '2014-01-06 10:01:25.122200',20, 44, 55, 54, '2015-08-20');
INSERT INTO OCEAN_BUOYS_SEQ VALUES(TIMESTAMP '2014-01-06 10:02:25.122200',21, 44, 55, 55, '2015-09-21');
INSERT INTO OCEAN_BUOYS_SEQ VALUES(TIMESTAMP '2014-01-06 10:03:25.122200',22, 44, 55, 56, '2015-10-22');
INSERT INTO OCEAN_BUOYS_SEQ VALUES(TIMESTAMP '2014-01-06 09:01:25.122200',23, 1, 55, 77,  '2015-11-23');
INSERT INTO OCEAN_BUOYS_SEQ VALUES(TIMESTAMP '2014-01-06 09:02:25.122200',24, 1, 55, 78,  '2015-12-24');
INSERT INTO OCEAN_BUOYS_SEQ VALUES(TIMESTAMP '2014-01-06 09:03:25.122200',25, 1, 55, 79,  '2016-01-25');
INSERT INTO OCEAN_BUOYS_SEQ VALUES(TIMESTAMP '2014-01-06 08:00:00.000000',26, 0, 55, 10,  '2016-02-26');
INSERT INTO OCEAN_BUOYS_SEQ VALUES(TIMESTAMP '2014-01-06 08:10:00.000000',27, 0, 55, 100, '2016-03-27');

/*Non-PTI Table*/

CREATE TABLE OCEAN_BUOYS_NONPTI(TIMECODE TIMESTAMP(6), BUOYID INTEGER, SALINITY INTEGER, TEMPERATURE INTEGER)

PRIMARY INDEX (BUOYID);

INSERT INTO OCEAN_BUOYS_NONPTI VALUES(TIMESTAMP '2014-01-06 08:00:00.000000', 0, 55, 10);
INSERT INTO OCEAN_BUOYS_NONPTI VALUES(TIMESTAMP '2014-01-06 08:08:59.999999', 0, 55, );
INSERT INTO OCEAN_BUOYS_NONPTI VALUES(TIMESTAMP '2014-01-06 08:09:59.999999', 0, 55, 99);
INSERT INTO OCEAN_BUOYS_NONPTI VALUES(TIMESTAMP '2014-01-06 08:10:00.000000', 0, 55, 10);
INSERT INTO OCEAN_BUOYS_NONPTI VALUES(TIMESTAMP '2014-01-06 08:10:00.000000', 0, 55, 100);
INSERT INTO OCEAN_BUOYS_NONPTI VALUES(TIMESTAMP '2014-01-06 09:01:25.122200', 1, 55, 77);
INSERT INTO OCEAN_BUOYS_NONPTI VALUES(TIMESTAMP '2014-01-06 09:01:25.122200', 1, 55, 70);
INSERT INTO OCEAN_BUOYS_NONPTI VALUES(TIMESTAMP '2014-01-06 09:02:25.122200', 1, 55, 78);
INSERT INTO OCEAN_BUOYS_NONPTI VALUES(TIMESTAMP '2014-01-06 09:02:25.122200', 1, 55, 71);
INSERT INTO OCEAN_BUOYS_NONPTI VALUES(TIMESTAMP '2014-01-06 09:03:25.122200', 1, 55, 79);
INSERT INTO OCEAN_BUOYS_NONPTI VALUES(TIMESTAMP '2014-01-06 09:03:25.122200', 1, 55, 72);
INSERT INTO OCEAN_BUOYS_NONPTI VALUES(TIMESTAMP '2014-01-06 21:01:25.122200', 2, 55, 80);
INSERT INTO OCEAN_BUOYS_NONPTI VALUES(TIMESTAMP '2014-01-06 21:02:25.122200', 2, 55, 81);
INSERT INTO OCEAN_BUOYS_NONPTI VALUES(TIMESTAMP '2014-01-06 21:03:25.122200', 2, 55, 82);
INSERT INTO OCEAN_BUOYS_NONPTI VALUES(TIMESTAMP '2014-01-06 10:00:24.000000', 44, 55, 43);
INSERT INTO OCEAN_BUOYS_NONPTI VALUES(TIMESTAMP '2014-01-06 10:00:24.333300', 44, 55, 43);
INSERT INTO OCEAN_BUOYS_NONPTI VALUES(TIMESTAMP '2014-01-06 10:00:25.122200', 44, 55, 43);
INSERT INTO OCEAN_BUOYS_NONPTI VALUES(TIMESTAMP '2014-01-06 10:00:26.122200', 44, 55, 43);
INSERT INTO OCEAN_BUOYS_NONPTI VALUES(TIMESTAMP '2014-01-06 10:01:25.122200', 44, 55, 53);
INSERT INTO OCEAN_BUOYS_NONPTI VALUES(TIMESTAMP '2014-01-06 10:01:25.122200', 44, 55, 54);
INSERT INTO OCEAN_BUOYS_NONPTI VALUES(TIMESTAMP '2014-01-06 10:02:25.122200', 44, 55, 53);
INSERT INTO OCEAN_BUOYS_NONPTI VALUES(TIMESTAMP '2014-01-06 10:02:25.122200', 44, 55, 55);
INSERT INTO OCEAN_BUOYS_NONPTI VALUES(TIMESTAMP '2014-01-06 10:03:25.122200', 44, 55, 53);
INSERT INTO OCEAN_BUOYS_NONPTI VALUES(TIMESTAMP '2014-01-06 10:03:25.122200', 44, 55, 56);
INSERT INTO OCEAN_BUOYS_NONPTI VALUES(TIMESTAMP '2014-01-06 10:12:00.000000', 44, 55, 43);
INSERT INTO OCEAN_BUOYS_NONPTI VALUES(TIMESTAMP '2014-01-06 10:32:12.122200', 44, 55, 43);
INSERT INTO OCEAN_BUOYS_NONPTI VALUES(TIMESTAMP '2014-01-06 10:52:00.000009', 44, 55, 43);