Had in interesting query from a client today. They have been storing start and stop times from a process in Oracle TIMESTAMP format and now want to get milliseconds out of the difference between the two timestamps. Seems pretty easy right?
Ok, first we create a table with two TIMESTAMP columns and an index value:
SQL> select * from check_time
SQL> /
TIM_COL1 TIM_COL2 TIME_INDEX
---------------------------- ---------------------------- ----------
05-JUL-06 05.00.42.437000 PM 05-JUL-06 05.01.54.984000 PM 1
05-JUL-06 05.03.14.781000 PM 05-JUL-06 05.03.39.328000 PM 2
Now, if we were just using DATE we could subtract the dates and use the proper multiplier to convert the fractional return to the proper time unit. However when we subtract TIMESTAMPs:
SQL> select tim_col2-tim_col1 from check_time;
TIM_COL2-TIM_COL1
------------------------------------------------
+000000000 00:01:12.547000
+000000000 00:00:24.547000
We get a hideous time interval upon which you can’t do math:
SQL> select sum(tim_col2-tim_col1) from check_time;
select sum(tim_col2-tim_col1) from check_time
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected NUMBER got INTERVAL
So what can be done?
In steps the new interval functions that allow extraction of timestamp components, such as DAY, HOUR, MINUTE and SECOND…but wait there is no MILLISECOND! Of course a short trip to the documentation shows that the SECOND has a fractional component that allows us to specify the number of decimals after the second thus giving us access to the milliseconds, even down to microseconds in the interval value, look here:
SQL> l
1* select sum(extract(second from tim_col2)-extract(second from tim_col1))*1000 from check_time
SQL> /
SUM(EXTRACT(SECONDFROMTIM_COL2)-EXTRACT(SECONDFROMTIM_COL1))
------------------------------------------------------------
37094
Well, that is more like it! So now we can get the milliseconds between and do the aggregation functions such as sum() and avg() on the results.
Subscribe to:
Post Comments (Atom)
2 comments:
This is great, except, if the number of seconds is greater than 60, then you need to worry about the minutes, i.e. you'll not get a value of 60 or greater from EXTRACT(SECOND ...). You'll need to add 60*EXTRACT(MINUTE ...) in this case. And so on.
thanx for the tip on timestamp/interval math. just made a function basing on this:
function get_seconds_from_interval(i_intrvl interval day to second) return number is
/*
return:
number of seconds & milliseconds (as a fractional part) encoded in interval variable
*/
begin
return extract(day from i_intrvl) * 86400 + extract(hour from i_intrvl) * 3600 + extract(minute from i_intrvl) * 60 + extract(second from i_intrvl);
exception
when others then begin return null; end;
end;
Post a Comment