Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Getting seconds between two Oracle Timestamps

Tags:

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!

like image 213
Peter Lang Avatar asked Oct 18 '10 09:10

Peter Lang


People also ask

How do you find the difference between two timestamps?

If you'd like to calculate the difference between the timestamps in seconds, multiply the decimal difference in days by the number of seconds in a day, which equals 24 * 60 * 60 = 86400 , or the product of the number of hours in a day, the number of minutes in an hour, and the number of seconds in a minute.

How can I find the difference between two timestamps in SQL?

To calculate the difference between the timestamps in MySQL, use the TIMESTAMPDIFF(unit, start, end) function. The unit argument can be MICROSECOND , SECOND , MINUTE , HOUR , DAY , WEEK , MONTH , QUARTER , or YEAR .

How can I find the difference between two dates in Oracle?

Answer: Oracle supports date arithmetic and you can make expressions like "date1 - date2" using date subtraction to get the difference between the two dates.

Does Oracle timestamp have milliseconds?

Answers. Dates do not contain milliseconds, they only go as fine as seconds. You might want to try a timestamp datatype. Valid date range from January 1, 4712 BC, to December 31, 9999 AD.


Video Answer


1 Answers

"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;
like image 195
Jeffrey Kemp Avatar answered Oct 03 '22 04:10

Jeffrey Kemp