INTERVAL data types

Not all database brands support a native SQL type to store time duration.

The INTERVAL data type in Genero BDL is used to store an amount of time.

There are two classes of INTERVAL types:
  • year-month interval, to represent a number of years and months like 9345 years, 10 months.
  • day-second interval, to represent a number of days and hours/minutes/seconds, such as 34 days, 5 hours, 20 minutes and 30.052 seconds.

While most database brands provide data types to store date/time information (as a point in time), only a few have a real native SQL type to store time duration.

If the database engine cannot store real interval data, the Genero ODI driver will use a CHAR(50) type, to provide a storage solution for INTERVAL variables. However, with interval data stored in a character string column, we lose the features offered by SQL to compute, filter, convert, and order time duration data in an efficient way. For example, a table index is more efficient, when the underlying column type corresponds to the actual stored data.

For maximum SQL portability, only use INTERVAL data, if the target database engine provides a native SQL type to store such data.

Consider also to use the INTERVAL qualifiers to match the native interval type:

  • INTERVAL YEAR(p) TO MONTH
  • INTERVAL DAY(p) TO MINUTE
  • INTERVAL DAY(p) TO SECOND
  • INTERVAL DAY(p) TO FRACTION(5)
Table 1. Interval data type support by database engine brands
Database Server Type Native interval type(s)?
IBM® Informix® Yes, Genero BDL is based on Informix SQL data types...
Microsoft™ SQL Server No, see INTERVAL with Microsoft SQL Server
Oracle® MySQL / MariadDB No, see INTERVAL with Oracle MySQL/MariaDB
Oracle Database Server Yes, see INTERVAL with Oracle DB
PostgreSQL Yes, see INTERVAL with PostgreSQL
SAP HANA® No, see INTERVAL with SAP HANA
SQLite No, see INTERVAL with SQLite
Dameng® Yes, see INTERVAL with Dameng