|
 |
|
Oracle Timestamp Elapsed Math
Oracle Database Tips by Donald Burleson |
by Mike Ault
Getting Milliseconds from Oracle Timestamps
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 how to we capture milliseconds?
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> select sum(
2 (extract(hour from tim_col2)-extract(hour from
tim_col1))*3600+
3 (extract(minute from tim_col2)-extract(minute from
tim_col1))*60+
4* extract(second from tim_col2)-extract(second from
tim_col1))*1000 ms from check_time
5 /
MS
---------
97094
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.
Question:
After converting a timestamp into milliseconds and then adding some
milliseconds in that value, Can we convert those milliseconds into a
new timestamp?
Answer:
This code will add milliseconds to a TIMESTAMP datatype:
select
to_char(systimestamp,'dd-mm-yyyy hh24:mi:ss.FF') as ts,
to_timestamp(
to_char(sysdate,'dd-mm-yyyy hh24:mi:ss.')
||
to_char(
to_number(
to_char(systimestamp,'FF')
) + 250),'dd-mm-yyyy
hh24:mi:ss.FF'
) as ts_1
from dual;
Also see
here for an example of inserting milliseconds into a column.
Start by defining column COL1 with datatype timestamp;
insert into
temp_table (COL1)
values
(timestamp '1999-12-01 11:30:55.3');
SQL> select col1 from temp_table;
COL1
---------------------------------------------
01-DEC-99 11.30.55.300000 AM
 |
If you like Oracle tuning, you may enjoy the new book "Oracle
Tuning: The Definitive Reference", over 900 pages
of BC's favorite tuning tips & scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts. |
|
and include the URL for the page.
