Teradata- DDL with column holding milliseconds only

Question

Is there a way i can create a column in a table which will only hold milliseconds value. I have a requirement where i have to store only LLLCCC(milliseconds) value in a column.

Answer 1

If you need to store a valid interval which you can manipulate as an interval (aggregate, perform interval arithmetic against, etc.) you can use the data type INTERVAL SECOND(1,6) in your table definition. If all values must be less than 1 second you can use a column level constraint to enforce the acceptable range of values.

CREATE VOLATILE TABLE MyTable, NO FALLBACK
(ColA SMALLINT NOT NULL,
 MyInterval INTERVAL SECOND(1,6) NOT NULL)
PRIMARY INDEX (ColA) ON COMMIT PRESERVE ROWS;

INSERT INTO MyTable VALUES (1, INTERVAL '0.244569' SECOND);

SELECT *
  FROM MyTable;

EDIT:

You can certainly store these values outside of their native format using CHAR(6), DECIMAL(6), or INTEGER. However, your ability to manipulate the values outside their native format becomes complicated.