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.
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 MONTHINTERVAL DAY(p) TO MINUTEINTERVAL DAY(p) TO SECONDINTERVAL DAY(p) TO FRACTION(5)
| Database Server Type | Native interval type(s)? |
|---|---|
| IBM® DB2® LUW | No, see INTERVAL with IBM
DB2 |
| IBM Informix® | Yes, Genero BDL is based on Informix SQL data types... |
| IBM Netezza® | No, see INTERVAL with IBM
Netezza |
| 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 |