RE: Getting seconds from interval type

From: Lex de Haan <lex.de.haan_at_naturaljoin.nl>
Date: Thu, 11 Aug 2005 12:24:22 +0200
Message-Id: <20050811102424.661D41DDCAF@turing.freelists.org>

oops -- undskyld to all. I feel pretty stupid. the LENGTH function only works on strings. and guess what? the length of my string expression just happened to be 19:  

SQL> select length(interval '9999' second) from dual;  

LENGTH(INTERVAL'9999'SECOND)

---

                          19
 

As punishment, I'll try to enter an enhancement request for the LENGTH function to accept intervals as well :-)

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 Lex de Haan
Sent: Thursday, August 11, 2005 12:15
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 - 05:27:18 CDT