13. 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.

13.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);

13.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.

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 }
);

13.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 }

13.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);

13.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. For example, to create an Oracledb IntervalDS object with the days``and ``seconds attributes set to 2 and 40 respectively is shown below. This example is used in subsequent sections.

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

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

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 }
);

13.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 }