Getting seconds between two Oracle Timestamps

Question

Tom Kyte suggests to use EXTRACT to get the difference:

extract( day from (x-y) )*24*60*60+
extract( hour from (x-y) )*60*60+
...

This seems to be harder to read and slower than this, for example:

( CAST( x AS DATE ) - CAST( y AS DATE ) ) * 86400

---

So, what is the way to get the difference between two Timestamps in seconds? Thanks!

Answer 1

I have always used the second way i.e. compare the DATEs (which gives you the number of days difference, with a fractional part), and the multiply by the factor you want to give you number of hours, minutes, seconds, or whatever.

I think it's good, and easy to read.

Answer 2

Personally, I find:

extract(day from (x-y))*24*60*60 + ... + extract(second from (x-y))

clearer in purpose than...

( CAST( x AS DATE ) - CAST( y AS DATE ) ) * 86400

to get the difference in seconds.

Tom's method takes a few more keystrokes but the intent is clear.

Answer 3

"Best Practice"

Whatever you do, wrap it in a function, e.g. seconds_between (from_date, to_date) - doesn't matter how it does it (choose the most efficient method) - then it will be perfectly obvious what your code is doing.

Performance

I tested the two methods on 11gR1 on my laptop (WinXP) with the test case below. It seems the CAST option is the fastest. (t1 is baseline, t2 used the extract method, t3 used the cast method)

t1 (nothing) 3
t2 (extract) 338
t3 (cast)    101

t1 (nothing) 3
t2 (extract) 336
t3 (cast)    100

Test script

declare
 x TIMESTAMP := SYSTIMESTAMP;
 y TIMESTAMP := TRUNC(SYSDATE);
 n PLS_INTEGER;
 lc CONSTANT PLS_INTEGER := 1000000;
 t1 PLS_INTEGER;
 t2 PLS_INTEGER;
 t3 PLS_INTEGER;
begin
 t1 := DBMS_UTILITY.get_time;
 for i in 1..lc loop
  n := i;
 end loop;
 t1 := DBMS_UTILITY.get_time - t1;
 t2 := DBMS_UTILITY.get_time;
 for i in 1..lc loop
  n := extract(day from (x-y))*24*60*60
     + extract(hour from (x-y))*60*60
     + extract(minute from (x-y))*60
     + extract(second from (x-y));
 end loop;
 t2 := DBMS_UTILITY.get_time - t2;
 t3 := DBMS_UTILITY.get_time;
 for i in 1..lc loop
  n := ( CAST( x AS DATE ) - CAST( y AS DATE ) ) * 86400;
 end loop;
 t3 := DBMS_UTILITY.get_time - t3;
 dbms_output.put_line('t1 (nothing) ' || t1);
 dbms_output.put_line('t2 (extract) ' || t2);
 dbms_output.put_line('t3 (cast)    ' || t3);
end;