Wednesday, January 28, 2015

Oracle - Find Time Difference (in Seconds) of Two Timestamp Columns

Suppose you have two timestamp columns in an Oracle table, from_timestamp and to_timestamp, and you want to find the difference in seconds of the two columns. 

You can achieve it by using the following SQL statement:


SELECT FROM_TIMESTAMP, TO_TIMESTAMP, 

  (

  to_number(TO_CHAR(FROM_TIMESTAMP, 'J') * 86400 + TO_CHAR(FROM_TIMESTAMP, 'SSSSS')) - 

  to_number(TO_CHAR(TO_TIMESTAMP, 'J') * 86400 + TO_CHAR(TO_TIMESTAMP, 'SSSSS'))

  ) 

  TIME_DIFF

FROM SOME_TABLE; 




Create a Successful Online Store at Bigcommerce! Try it Free Now!

No comments:

Post a Comment