6. Working with INTERVAL Data Type - Teradata Vantage

Apache Iceberg and Delta Lake Open Table Format on VantageCloud Lake Getting Started

Deployment
VantageCloud
Edition
Lake
Product
Teradata Vantage
Published
December 2024
ft:locale
en-US
ft:lastEdition
2025-01-03
dita:mapPath
bsr1702324250454.ditamap
dita:ditavalPath
pny1626732985837.ditaval
dita:id
bsr1702324250454

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 type cast 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