Getting seconds between two Oracle Timestamps

问题:

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!

回答1:

"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;

回答2:

Alternative:
I found this to work as well to get the difference in seconds including milliseconds.
It's even save for time-zones with "daylight saving" while the extract method would have a problem. Unfortunately the difference between t1 and t2 is limited for the result to be right. Casting timestamps to date format is not an option because the fractions of seconds are lost.

select (sysdate + (t2 - t1)*1000 - sysdate) * 86.4 from 
(select  
    to_timestamp('2014-03-30 01:00:10.111','YYYY-MM-DD HH24:MI:SS.FF') at time zone 'MET' t1, 
    to_timestamp('2014-03-30 03:00:10.112','YYYY-MM-DD HH24:MI:SS.FF') at time zone 'MET' t2 
 from dual);

回答3:

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.

回答4:

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.

回答5:

to_number(to_char(t2, 'yyyymmddhh24missff')) - to_number(to_char(t1, 'yyyymmddhh24missff'))