Oracle: Difference between two Timestamps, in seconds…

In oracle, if you subtract two timestamps, the result is of type “interval”. Its somewhat cumbersome to get the total number of seconds this interval represents. Here’s an Oracle function which will yield the total number of seconds between two timestamps :

CREATE OR REPLACE function timestamp_diff_in_seconds (ts1 in timestamp, ts2 in timestamp)
       return number is total_secs number;
       diff interval day(9) to second(6);
   begin
   diff := ts2 - ts1;
   total_secs := abs(extract(second from diff) + extract(minute from diff)*60 + extract(hour from diff)*60*60 + extract(day from diff)*24*60*60);

   return total_secs;
end timestamp_diff_in_seconds;

Using the above, we can get the toal time in seconds, between two timestamp parameters (effectively converts the “interval” difference of the two timestamps into to the total number of seconds)

Advertisements

11 Responses to Oracle: Difference between two Timestamps, in seconds…

  1. M Siddiqui says:

    It is an excellent function to clearly show how oracle uses the timestamp and how to calcuate the time difference between two timestamps. I could not find the same thing in oracle manual or any where else. Good work. 5 stars *****.

  2. Frans W says:

    Most excellent! Consider it “put to good use” 🙂

  3. Pingback: Converting an Interval Day to Second to an Integer - Page 3 - dBforums

  4. devin says:

    This is all good, but what about the fractional seconds? This function rounds the interval to the nearest second and throws away the fractional part.

    Is there any way to include the fractional part?

  5. Bilal Haider says:

    Its exaxctly what I need,,,, Very helpful post….

  6. Nicolai Henriksen says:

    In some circumstances “1 day” and “24 hours” is not the same.

    I mean if the two timestamps are on different sides of daylight savings your calculation would be wrong? Or are plain timestamp values ignorant of time zones?

    • Raju Rathi says:

      I think , below will also work :-

      CREATE OR REPLACE function timestamp_diff_in_seconds (ts1 in timestamp, ts2 in timestamp)

      return number is total_secs number;

      begin

      total_secs := (EXTRACT(DAY FROM ((ts1-ts2)*24*60*60)) / (24 * 60 *60.00)) ;

      return total_secs;

      end timestamp_diff_in_seconds;

  7. diyasher says:

    very helpful post

  8. Garg says:

    I need some help..

    I want to fetch the record when exec_time > 5:15:25 . Can u please help on this.
    Which function and how to write it.

    exec_time
    1:05:11
    5:09:45
    11:15:20
    22:12:12

  9. colemar says:

    As devin told, the difference does not include fractional seconds.
    The same result could be obtained more simply by the expression:
    (cast(ts2 as date) – cast(ts1 as date))*24*60*60

  10. Bill says:

    You just saved me a metric ton of hacking and cursing, these two hours before the deadline of a project that has gone on for almost a year, for one last finicky stupid passive check that must be done before 10 this morning.

    I thought you should know. THANK YOU!

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

%d bloggers like this: