'interval day to second' type gives Error: NJS-010: unsupported data type 2015

[Chinmoy-globalIds]

Following JS error is thrown when i execute the following query using node oracle db. JSERROR: Error: NJS-010: unsupported data type 2015 in column 2 QUERY: select "USED", "MEASUREONLY_CUMTIME", "STATS2", "MANAGED_CUMTIME", "MONITOR", "MEASUREONLY", "MONITOR_CUMTIME", "TIMESTAMP", "STATS1", "PREVMODE", "MANAGED", "MODEBTIME", "MAXPC", "CURNUMPC", "FEATURE_INFO", "STATS3", "CURMODE" from "APPQOSSYS"."WLM_FEATURE_USAGE" WHERE ROWNUM <= 500 The data type of MEASUREONLY_CUMTIME column is interval day to second.

[anthony-tuininga]

Yes, that type is not supported. Did you want it to be supported and if so, what Javascript type would you suggest be used?

[dmcghan]

Just throwing this out there, perhaps a number that represents the number of milliseconds would work. That should store intervals up to 285,616 years if this is right:

const jsMaxNumber = 9007199254740991;
const ms = 1000;
const secondsInMinute = 60;
const minutesInHour = 60;
const hoursInDay = 24;
const daysInYear = 365;

// 285,616
console.log(jsMaxNumber/ms/secondsInMinute/minutesInHour/hoursInDay/daysInYear);

The value could be easily converted into a duration with a library like Moment.js: https://momentjs.com/docs/#/durations/creating/

[anthony-tuininga]

Note that Oracle's day to second interval supports nanoseconds, so using milliseconds would drop that capability. Perhaps a Javascript object could be returned instead that looks like this?

const interval = {
    days : 1,
    hours : 5,
    minutes : 10,
    seconds : 35,
    nanoseconds : 123456789
};

The ISO 8601 format string and the total number of milliseconds could also be added, if desired, or utility functions added to convert to these formats. Thoughts?

[dmcghan]

That would be the equivalent of this, right?

select interval '1 5:10:35.123456789' day to second(9)
from dual

It's nice to have the precision. And this might work well for basic formatting too.

Also, it seems Moment would support this already:

var dbInterval = {
    days : 1,
    hours : 5,
    minutes : 10,
    seconds : 35,
    nanoseconds : 123456789
};

var momentDuration = moment.duration({
    days: dbInterval.days,
    hours: dbInterval.hours,
    minutes: dbInterval.minutes,
    seconds: dbInterval.seconds,
    milliseconds: dbInterval.nanoseconds/1000000
});

console.log(momentDuration);

Does that seem about right?

[anthony-tuininga]

Yes. That should work. And we could include milliseconds in the dbInterval object that is returned all the time if you think that the moment module would be the most common usage of this? I presume that extra keys (like nanoseconds) would simply be ignored. It looks like this would also cover the Oracle year/month intervals as well if that was the case.

[dmcghan]

Maybe sumMilliseconds or totalMilliseconds? If you can do it efficiently, then I'd say go for it!

[anthony-tuininga]

We would want it to be called milliseconds, wouldn't we? That way it could be passed directly to moment.duration() without having to be rebuilt as a new object. Or am I missing something?

[dmcghan]

Maybe I'm missing something :)

As I understand it, the values of the fields are additive - you'd have to add the days, hours, minutes, etc. to get to a "total" duration/interval of time. The nanoseconds field would be in line with this, just a small part of the total interval.

The milliseconds field would be different in that it would be the total of all the fields represented in milliseconds (that's what Moment.js expects for the simple duration syntax). I thought using a name like totalMilliseconds would help folks understand that it's a little different.

[anthony-tuininga]

Ah! I understand what you are saying now and that could be a source of confusion. :-) In that case, I think it would make sense to leave it out completely.

[dmcghan]

What! :) It would make things much easier to have it available. Otherwise, the mapping to Moment.js requires a full object and the division. But I'd only support it if it was a lightweight thing on the OCI side.

Alternatively, a getTotalMilliseconds method could be exposed.

[myzonjkee]

I have a same problem. Is there a workaround how to make it work?

[sharadraju]

@myzonjkee Thanks for using node-oracledb.

Please state your exact use case and provide a sample code, if possible.

We currently do not support the interval day to second type.