CODEWITHSUNDEEP
oracle-database
user1148952
user1148952

Reputation: 39

how to find the difference between time stamp in oracle?

I have to find the difference between these two timestamps please help me

27-FEB-12 02.11.31.910000000 AM, 27-FEB-12 02.11.49.002000000 AM

Upvotes: 0

Views: 2661

Answers (2)

Tebbe
Tebbe

Reputation: 1372

For more control over how your output is displayed, you can use EXTRACT:

SQL> SELECT TO_CHAR(EXTRACT(HOUR   FROM (x.ts2 - x.ts1)) ,'fm00')                     hours
  2  ,      TO_CHAR(EXTRACT(MINUTE FROM (x.ts2 - x.ts1)) ,'fm00')                     minutes
  3  ,      TO_CHAR(EXTRACT(SECOND FROM (x.ts2 - x.ts1)) ,'fm00.' || RPAD('0',9,'0')) seconds
  4  FROM (SELECT TO_TIMESTAMP('27-FEB-12 02.11.31.910000000 AM', 'DD-MON-RR HH.MI.SS.FF9 AM') ts1
  5        ,      TO_TIMESTAMP('27-FEB-12 02.11.49.002000000 AM', 'DD-MON-RR HH.MI.SS.FF9 AM') ts2
  6        FROM   DUAL) x
  7  ;

HOU MIN SECONDS
--- --- -------------
00  00  17.092000000

SQL>

See http://docs.oracle.com/cd/B28359_01/server.111/b28286/functions052.htm.

Upvotes: 1

Justin Cave
Justin Cave

Reputation: 231851

Subtract them. The result will be an INTERVAL data type representing, in this case, 17.092 seconds.

SQL> ed
Wrote file afiedt.buf

  1  select to_timestamp( '27-FEB-12 02.11.31.910000000 AM', 'DD-MON-RR HH.MI.SS.FF9 AM') -
  2         to_timestamp( '27-FEB-12 02.11.49.002000000 AM', 'DD-MON-RR HH.MI.SS.FF9 AM')
  3*   from dual
SQL> /

TO_TIMESTAMP('27-FEB-1202.11.31.910000000AM','DD-MON-RRHH.MI.SS.FF9AM')-TO_
---------------------------------------------------------------------------
-000000000 00:00:17.092000000

Upvotes: 2

Related Questions