• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1001
  • Last Modified:

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
0
samir25
Asked:
samir25
  • 4
  • 3
1 Solution
 
dalebettsCommented:
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
 
sujith80Commented:
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
 
samir25Author 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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
sujith80Commented:
>> 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

Open in new window

0
 
samir25Author 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
 
samir25Author Commented:
hi any help ? pls help clarify the abt doubt
0
 
samir25Author Commented:
hi sujith can u pls help clarify this.. i really need an exp feedback.
0
 
sujith80Commented:
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

Open in new window

0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now