# what does numtodsinterval imply

hi i ve read a lot abt numtodsinterval on the net but stil i am unable to understand what it means.

here are some eg from net
numtodsinterval(150, 'DAY') would return '+000000150'
numtodsinterval(1500, 'HOUR') would return '+000000062'
numtodsinterval(15000, 'MINUTE') would return '+000000010'
numtodsinterval(150000, 'SECOND') would return '+000000001'

can someone tell me how 150 is converted into day and 1500 into hr.. i am totally confused...pls pls help clarify

thanks
LVL 1
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
Good question, i'm intrigued on finding the answer to this also. I have a my own idea of what it is but i'm not 100% sure.
0
Data ArchitectCommented:
It doesn't really return the data the way you have shown here.
If you run from sqlplus you can see,

SQL> select numtodsinterval(1500, 'HOUR') from dual;

NUMTODSINTERVAL(1500,'HOUR')
----------------------------------------------------
+000000062 12:00:00.000000000

The results can be interpreted as - 1500 HOURS is equal to 62 days + 12 hours.

The NUMTODSINTERVAL function converts the first argument(In the above example 1500), expressed in units of the second argument(in the above example HOURS), to a INTERVAL DAY TO SECOND format.
0
Author Commented:
NUMTODSINTERVAL(150000,'SECOND')
---------------------------------
+000000001 17:40:00.000000000

can u help me decipher this one. so 150000/60/60/24 returns 2 days.

so how do i get 1 day 17hrs and 40 mins?
0
Data ArchitectCommented:
>> 150000/60/60/24 returns 2 days.
how? to me it gives 1.73....

SQL> select 150000 / 60 / 60 / 24 from dual;

150000/60/60/24
---------------
1.73611111

>>How to decipher it?
See the explanation.
``````SQL> select NUMTODSINTERVAL(150000,'SECOND')
from dual;

NUMTODSINTERVAL(150000,'SECOND')
---------------------------------------------
+000000001 17:40:00.000000000

SQL> select (1 * 24 * 60 * 60 ) + ( 17 * 60 * 60 ) + ( 40 * 60 ) from dual
/

(1*24*60*60)+(17*60*60)+(40*60)
-------------------------------
150000
``````
0
Author Commented:
ok i understand.

but here is one confusion ...
if i use NUMTODSINTERVAL to convert in days ==numtodsinterval(tot_cyc,   'DAY')
i get this output (for one of my cases) ==> 02 22:09
i converted this into minutes as (2 days * 24 hrs * 60 mins) + 22 mins= 2902

where if i dont use numtodsinterval for my total cycle time i get this output
==> 2.92300926       i believe this is in days. so i converted into minutes as below
==> 2.92300926 * 24 * 60 i get this 4209.133334
why does the final output vary for the same number. same cycle time.

pls enlighten

0
Author Commented:
hi any help ? pls help clarify the abt doubt
0
Author Commented:
hi sujith can u pls help clarify this.. i really need an exp feedback.
0
Data ArchitectCommented:
22 is not minutes, it is the number of hours.
``````SQL> select numtodsinterval(2.92300926, 'DAY') from dual;

NUMTODSINTERVAL(2.92300926,'DAY')
-----------------------------------------------------------------
+000000002 22:09:08.000064000

SQL> select (2 * 24 * 60) + (22 * 60) + 9 + (8 / 60 ) from dual;

(2*24*60)+(22*60)+9+(8/60)
--------------------------
4209.13333

SQL> select 2.92300926 * 24 * 60 from dual;

2.92300926*24*60
----------------
4209.13333
``````
0

Experts Exchange Solution brought to you by