14. Using INTERVAL Data

Oracle Database supports two INTERVAL data types that store time durations - INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND. For more information on these data types, see Oracle Interval Types.

Node-oracledb does not support using INTERVAL data types in Oracle Database Objects.

14.1. Using INTERVAL YEAR TO MONTH Data

The INTERVAL YEAR TO MONTH data type stores a period of time using years and months.

To create a table with a column for INTERVAL YEAR TO MONTH data, for example:

CREATE TABLE TableIntervalYM (IntervalCol INTERVAL YEAR TO MONTH);

14.1.1. Inserting INTERVAL YEAR TO MONTH

You must create an IntervalYM object using Oracledb IntervalYM Class to insert into an INTERVAL YEAR TO MONTH column. For example:

const interval = new oracledb.IntervalYM();

This creates an Oracledb IntervalYM object with years and months attributes set to 0. You can also create an IntervalYM object with zero intervals using:

const interval = oracledb.IntervalYM({});

You can define the optional years and months attributes in the IntervalYM class. For example, to create an Oracledb IntervalYM cobject with only the years attribute set to 2:

const interval = new oracledb.IntervalYM({ years: 2 });

An example of creating an IntervalYM object with years and months attributes set to 1 and 6 respectively is shown below. This example is used in subsequent sections.

const interval = new oracledb.IntervalYM({ years: 1, months: 6 });

If you specify non-integer values in the attributes of IntervalYM object, then the NJS-007 error is raised.

Note that node-oracledb normalizes the months value when it exceeds or falls below its standard value.

You can insert IntervalYM objects into an INTERVAL YEAR TO MONTH column by binding as oracledb.DB_TYPE_INTERVAL_YM, for example:

await connection.execute(
    `INSERT INTO TableIntervalYM VALUES (:bv)`,
    { bv: { val: interval, type: oracledb.DB_TYPE_INTERVAL_YM }
);

14.1.2. Fetching INTERVAL YEAR TO MONTH

To query an INTERVAL YEAR TO MONTH column, you can use:

const result = await connection.execute(`SELECT * FROM TableIntervalYM`);
console.log(result.rows[0][0]);

This query prints:

IntervalYM { years: 1, months: 6 }

14.2. Using INTERVAL DAY TO SECOND Data

The INTERVAL DAY TO SECOND data type stores a period of time using days, hours, minutes, seconds, and fractional seconds.

To create a table with a column for INTERVAL DAY TO SECOND data, for example:

CREATE TABLE TableIntervalDS (IntervalDSCol INTERVAL DAY TO SECOND);

14.2.1. Inserting INTERVAL DAY TO SECOND

You must create an IntervalDS object using Oracledb IntervalDS Class to insert into an INTERVAL DAY TO SECOND column. For example:

const interval = oracledb.IntervalDS();

This creates an Oracledb IntervalDS object with days, hours, minutes, seconds, and fseconds (fractional seconds) attributes set to 0. You can also create an IntervalDS object with zero intervals using:

const interval = oracledb.IntervalDS({});

You can define the optional days, hours, minutes, seconds, and fseconds attributes in the IntervalDS object. An example to create an Oracledb IntervalDS object with the days and seconds attributes set to 2 and 40 respectively is shown below.

const data = new oracledb.IntervalDS({ days: 2, seconds: 40 });

The above example is used in subsequent sections.

If you specify non-integer values in the attributes of IntervalDS object, then the NJS-007 error is raised.

Note that node-oracledb normalizes the hours, minutes, and seconds values when they exceed or fall below their standard values.

You can insert IntervalDS objects into an INTERVAL DAY TO SECOND column by binding as oracledb.DB_TYPE_INTERVAL_DS, for example:

await connection.execute(
    `INSERT INTO TableIntervalDS VALUES (:bv)`,
    { bv: { val: data, type: oracledb.DB_TYPE_INTERVAL_DS }
);

14.2.2. Fetching INTERVAL DAY TO SECOND

To query an INTERVAL DAY TO SECOND column, you can use:

const result = await connection.execute(`SELECT * FROM TableIntervalDS`);
console.log(result.rows[0][0]);

This query prints:

IntervalDS { days: 2, hours: 0, minutes: 0, seconds: 40, fseconds: 0 }