Vantage creates up to three columns automatically in PTI tables, based on the PTI specification in the table definition:
- TD_TIMECODE holds the timestamp data from each sensor reading in the time series. This column is functionally the first column in the table. When you insert or load data into a PTI table, the timestamp associated with the data must be the first value inserted for each row. The data type of the column is determined by the timecode_data_type specified in the PRIMARY TIME INDEX clause.
- TD_SEQNO is generated only if the PTI definition includes the SEQUENCED keyword, indicating that the table is a sequenced table. Sequenced tables are likely to have multiple rows from the same sensor ID have the same timestamp, usually as a result of sensor data readings taken more frequently than the granularity chosen for the timecode_data_type. The sequence number in the TD_SEQNO column serves to distinguish different rows that share the same timestamp value. This column must be populated by your data collection application, and is neither populated nor maintained by Vantage. TD_SEQNO is the second column in a sequenced PTI table.
- TD_TIMEBUCKET is generated only if the PTI definition includes a timebucket_duration value. It is managed by the database, cannot be directly manipulated by SQL code, and does not appear in the output of any query. It is mentioned here only because it appears in the output of SHOW TABLE statements executed on these types of PTI tables.
Although these columns are generated automatically by the database, you can explicitly specify the TD_TIMECODE and TD_SEQNO columns in your PTI table definition if you want to specify a FORMAT or TITLE for the columns. If you want to specify any of these columns, you must explicitly specify all of the automatically generated columns for the table (as appropriate, based on the parameters you add to the PTI). Use the syntax shown below and add your FORMAT and TITLE specifications after the main column definition:
-
TD_TIMEBUCKET BIGINT NOT NULL GENERATED SYSTEM TIMECOLUMN
You must specify the TD_TIMEBUCKET column if your PRIMARY TIME INDEX clause includes a timebucket_duration parameter. This column is normally hidden and inaccessible to queries.You cannot add FORMAT or TITLE column attributes for the TD_TIMEBUCKET column.
-
TD_TIMECODE timecode_data_type NOT NULL GENERATED TIMECOLUMN
where timecode_data_type must match the timecode_data_type used in the PTI definition clause.
- TD_SEQNO INT NOT NULL GENERATED TIMECOLUMN