want to get diff of two timestamps in millisec in stored procedure

Question

I have a problem with this code:

select pick_id 
into pickid 
from prov_adapter_pmonitor 
where pick_id = 1;

select count(*) 
into no_of_picks 
from prov_adapter_pmonitor;

WHILE pickid < no_of_picks
loop
    SELECT pick_end_time
    INTO ts1
    FROM prov_adapter_pmonitor
    WHERE pick_id=pickid
    AND count  > 0;

    SELECT pick_start_time
    INTO ts2
    FROM prov_adapter_pmonitor
    WHERE pick_id=pickid+1;

    SELECT TIMESTAMPDIFF(SECOND,ts2,ts1) from prov_adapter_pmonitor;
    select count into cnt from prov_adapter_pmonitor where pick_id=pickid;

    rslt := rslt+diff;
    cnt1 :=cnt1+cnt;

   pickid  :=pickid       +1;

end loop;

I want diff interms of millisec of my two timestamps ts1 and ts2 ,these timestamps are there in my entity, but it is giving exception at third select statement

Please give me any idea regarding this

oracle 11g and that is procedure it compiles successfully but while running it is giving error timestampdiff select statement — mqsuresh, Dec 22 '14 at 06:28
*What error?* I think timestampdiff is _not_ an Oracle-Function but mysql... — evilive, Dec 22 '14 at 06:32
Excuse...it is an Oracle Function ;) What ist the Error-Message/code? i.e. ORA-12345 — evilive, Dec 22 '14 at 06:34
`timestampdiff` is a TimesTen function, not an Oracle RDBMS function (see [the docs](http://docs.oracle.com/cd/E21901_01/timesten.1122/e21634/oracle_tt.htm#TTCAC350)). — Alex Poole, Dec 22 '14 at 08:21
ok,,,but how to get the diff of the two timestamps in milliseconds for example ts2 and ts1 contains the two timestamps then i want total diff in milliseconds — mqsuresh, Dec 22 '14 at 08:46
Bit late, but possible duplicate of [this question](http://stackoverflow.com/q/22606161/266304). — Alex Poole, Dec 25 '14 at 12:52

Andreas Wederbrand · Accepted Answer · 2014-12-22T07:21:09.810

1

It looks like you are mixing mysql and oracle syntax. I think what you need is EXTRACT

select extract(second from (ts2 - ts1)) as diff from dual;

edited Dec 22 '14 at 07:21

answered Dec 22 '14 at 06:27

Andreas Wederbrand

38,065
11
68
78

Error(45,74): PL/SQL: ORA-00923: FROM keyword not found where expected – mqsuresh Dec 22 '14 at 06:35
Oh, there wasn't any information about database when I answered. You can try with `from dual` for Oracle – Andreas Wederbrand Dec 22 '14 at 07:17
it is giving only seconds but i want total seconds like hours,mins are also coverted into sec and added to seconds what we extracted – mqsuresh Dec 22 '14 at 08:43
Just extract those parts and add it all together. – Andreas Wederbrand Dec 22 '14 at 12:25

want to get diff of two timestamps in millisec in stored procedure

1 Answers1