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)
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 *****.
Comment by M Siddiqui — March 2, 2010 @ 10:45 am |
Most excellent! Consider it “put to good use”
Comment by Frans W — March 2, 2010 @ 12:05 pm |
[...] [...]
Pingback by Converting an Interval Day to Second to an Integer - Page 3 - dBforums — March 31, 2010 @ 12:05 pm |
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?
Comment by devin — February 1, 2011 @ 6:39 am |
Its exaxctly what I need,,,, Very helpful post….
Comment by Bilal Haider — July 28, 2011 @ 12:19 am |
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?
Comment by Nicolai Henriksen — August 9, 2011 @ 2:23 am |
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;
Comment by Raju Rathi — August 24, 2011 @ 8:01 am |
very helpful post
Comment by diyasher — December 9, 2011 @ 1:45 am |
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
Comment by Garg — January 24, 2012 @ 8:23 am |
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
Comment by colemar — April 23, 2012 @ 8:51 am |