RE: Getting seconds from interval type

  • From: "Powell, Mark D" <mark.powell@xxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Thu, 11 Aug 2005 08:46:33 -0400

Lex I do not see how that works.  Try substituting a different value in
your query.
 
ddc1 > select (length(interval '3600' SECOND) + 1) * 60
  2  from dual;
 
(LENGTH(INTERVAL'3600'SECOND)+1)*60
-----------------------------------
                               1200

 
-- Mark D Powell --

 
________________________________

From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Lex de Haan
Sent: Thursday, August 11, 2005 6:15 AM
To: AmihayG@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: RE: Getting seconds from interval type


Hi Amihay,
 
what about using the length function? 
after all, that's what you want to derive -- the length of an interval.
see below:
 
SQL> select (length(interval '1200' second)+1)*60
  2  from dual;
 
(LENGTH(INTERVAL'1200'SECOND)+1)*60
-----------------------------------
                               1200


as you can see, you have to adjust the length (+1)

and then you multiply with 60 to get seconds. hope this helps,

kind regards,

Lex.

------------------------------------------------------------------
Steve Adams Seminar http://www.naturaljoin.nl/events/seminars.html
<http://www.naturaljoin.nl/events/seminars.html> 
------------------------------------------------------------------ 

 

________________________________

From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Amihay Gonen
Sent: Thursday, August 11, 2005 07:56
To: oracle-l@xxxxxxxxxxxxx
Subject: Getting seconds from interval type


Hi, 
 
I can easily convert number from interval :

select numtodsinterval(1200,'SECOND') from dual;


NUMTODSINTERVAL(1200,'SECOND')
------------------------------------------------------------------------
---
+000000000 00:20:00.000000000



But I've found no place where I can convert interval to number again. 


I've found  workaround :

 select extract( SECOND from numtodsinterval(1200,'SECOND'))+
           extract( MINUTE from numtodsinterval(1200,'SECOND'))*60+
           extract( HOUR from numtodsinterval(1200,'SECOND'))*60*60+
           extract( DAY from numtodsinterval(1200,'SECOND'))*60*60*24
  from dual;
 

but this is cumbersome process , I would like to see something like
intervaldstonum(interval,'SECOND') ....

 

Amihay Gonen
DBA,
972-3-90021678

 

 

Other related posts: