5. Working with INTERVAL Data Type - Teradata Vantage

In Iceberg and Delta Lake, internally, INTERVAL types are mapped to the STRING data type. Because the database does not know the precision of the INTERVAL types, errors can occur when reading and writing INTERVAL data types. So, when reading and writing INTERVAL, values must be explicitly typecast to match the column precision value so overflow errors do not occur.

Example

• When creating a table with an INTERVAL column, specify the precision for the INTERVAL type. create table delta_unity.db1.tbl1 (a interval year(3));
  HELP TABLE will show that the column type is STRING/VARCHAR.

  help table delta_unity.db1.tbl1;
   *** Help information returned. One row.
   *** Total elapsed time was 2 seconds.
  ColumnName a
  DeltaType string
  TeradataInternalType VARCHAR_DT(2000) CHARACTER SET UNICODE
  TeradataType VARCHAR(2000) CHARACTER SET UNICODE

• Insert value of same precision into table. From our table definition, the precision is 3.

  insert into delta_unity.db1.tbl1 values (interval '555' year);

• When executing a select query, specify the precision of the column in the condition.

  sel * from delta_unity.db1.tbl1 where a (interval year(3)) > interval '10' year;
   *** Query completed. One row found. One column returned.
   *** Total elapsed time was 3 seconds.
  a
  ---------------------------------------------------------------------------
   555
  sel * from delta_unity.db1.tbl1 where a (interval year(3)) < interval '888' year;
   *** Query completed. One row found. One column returned.
   *** Total elapsed time was 3 seconds.
  a
  ---------------------------------------------------------------------------
   555