Working with Oracle Timestamp

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s