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!
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.
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 .
Answer: Oracle supports date arithmetic and you can make expressions like "date1 - date2" using date subtraction to get the difference between the two dates.
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.
"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;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With