SQL> SELECT TIMESTAMP,USAGETIME,TIMESTAMP - numtodsinterval(USAGETIME,'second') from TBLCDR WHERE ROWNUM <= 10; TIMESTAMP USAGETIME TIMESTAMP-NUMTODSINTERVAL(USAGETIME,'SECOND') --------------------------------------------------------------------------- ---------------------------------------------------------------- ---------------------------------------- 18-MAY-14 10.09.52.778000 AM 20 18-MAY-14 10.09.32.778000000 AM 20-MAY-14 10.10.12.778000 AM 30 20-MAY-14 10.09.42.778000000 AM 20-MAY-14 10.10.42.778000 AM 20 20-MAY-14 10.10.22.778000000 AM 20-MAY-14 10.11.02.778000 AM 20 20-MAY-14 10.10.42.778000000 AM 20-MAY-14 10.09.52.778000 AM 20 20-MAY-14 10.09.32.778000000 AM 20-MAY-14 10.10.32.778000 AM 40 20-MAY-14 10.09.52.778000000 AM 20-MAY-14 10.10.47.778000 AM 15 20-MAY-14 10.10.32.778000000 AM 20-MAY-14 10.10.55.778000 AM 8 20-MAY-14 10.10.47.778000000 AM 20-MAY-14 10.11.33.778000 AM 28 20-MAY-14 10.11.05.778000000 AM 20-MAY-14 10.11.53.778000 AM 20 20-MAY-14 10.11.33.778000000 AM 10 rows selected.
SQL> SELECT TIMESTAMP,USAGETIME,to_char(timestamp - (usagetime/86400),'DD-MON-YY HH24-MI-SS') FROM TBLCDR WHERE ROWNUM <=10; TIMESTAMP USAGETIME TO_CHAR(TIMESTAMP-(USAGETIM --------------------------------------------------------------------------- ---------------------------------------------------------------- --------------------------- 18-MAY-14 10.09.52.778000 AM 20 18-MAY-14 10-09-32 20-MAY-14 10.10.12.778000 AM 30 20-MAY-14 10-09-42 20-MAY-14 10.10.42.778000 AM 20 20-MAY-14 10-10-22 20-MAY-14 10.11.02.778000 AM 20 20-MAY-14 10-10-42 20-MAY-14 10.09.52.778000 AM 20 20-MAY-14 10-09-32 20-MAY-14 10.10.32.778000 AM 40 20-MAY-14 10-09-52 20-MAY-14 10.10.47.778000 AM 15 20-MAY-14 10-10-32 20-MAY-14 10.10.55.778000 AM 8 20-MAY-14 10-10-47 20-MAY-14 10.11.33.778000 AM 28 20-MAY-14 10-11-05 20-MAY-14 10.11.53.778000 AM 20 20-MAY-14 10-11-33
http://docs.oracle.com/cd/B12037_01/server.101/b10759/functions093.htm
