RE: Getting seconds from interval type

From: Powell, Mark D <mark.powell_at_eds.com>
Date: Thu, 11 Aug 2005 08:46:33 -0400
Message-ID: <5A14AF34CFF8AD44A44891F7C9FF41050395BA2F@usahm236.amer.corp.eds.com>

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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Lex de Haan Sent: Thursday, August 11, 2005 6:15 AM
To: AmihayG_at_ectel.com; oracle-l_at_freelists.org 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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Amihay Gonen Sent: Thursday, August 11, 2005 07:56
To: oracle-l_at_freelists.org
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

 

 

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Aug 11 2005 - 07:49:23 CDT