Home » SQL & PL/SQL » SQL & PL/SQL » difference between two timestamps
- difference between two timestamps [message #236228] Wed, 09 May 2007 06:33 Go to next message
soujanya_srk
Messages: 111
Registered: November 2006
Location: HYDERABAD
Senior Member
i know how to calculate time difference in seconds given
two dates, (using to_date() - to_date())

i am testing the performance of a function, and am capturing
the systimestamp in one variable(bs)

bs:=systimestamp immediately after begin

and before end, i say ls:=systimestamp

now i want a query to find out the difference between these
two variables...basically, its the difference in milliseconds
between two dates...

so that i can fetch it into another variable, and then see
how fast my function is performing

can anyone please tell me how to find out difference between
2 systimestamps?

that is difference in milliseconds

- Re: difference between two timestamps [message #236236 is a reply to message #236228] Wed, 09 May 2007 06:48 Go to previous messageGo to next message
Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
SQL> declare
  2    ts timestamp := systimestamp;
  3    it interval day to second(3);
  4    ms integer;
  5  begin
  6    dbms_lock.sleep (3); -- wait 3 seconds;
  7    it := systimestamp - ts;
  8    dbms_output.put_line('Delay: '||it);
  9    ms := to_number(translate(substr(it,4),'0:.','0'));
 10    dbms_output.put_line('Delay (ms): '||ms);
 11  end;
 12  /
Delay: +00 00:00:03.000
Delay (ms): 3000

PL/SQL procedure successfully completed.

Regards
Michel
- Re: difference between two timestamps [message #236243 is a reply to message #236236] Wed, 09 May 2007 07:06 Go to previous messageGo to next message
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Well, Michel was faster. Wink

But this here

declare
v_ts1 TIMESTAMP;
v_ts2 TIMESTAMP; 
v_diff INTERVAL DAY TO SECOND;
v_diff_ms NUMBER;

BEGIN

v_ts1 := systimestamp;
Dbms_Lock.sleep(5.3);
v_ts2 := systimestamp;

Dbms_Output.put_line ( 'TS1 : ' || To_Char(v_ts1,'hh24:mi:ss ff6') ||  ' / TS2: ' || To_Char(v_ts2,'hh24:mi:ss ff6') );


v_diff := v_ts2 - v_ts1;
Dbms_Output.put_line ( 'Difference as Interval : ' || v_diff );

v_diff_ms := ( extract(hour from v_diff)   * 3600 +
               extract(minute from v_diff) * 60   +
               extract(second from v_diff) ) 
             * 1000; 
Dbms_Output.put_line ( 'Difference as MS     : ' || v_diff_ms );

END;



is the solution I came up with.
- Re: difference between two timestamps [message #236251 is a reply to message #236243] Wed, 09 May 2007 07:23 Go to previous messageGo to next message
Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Unfortunatly EXTRACT can't extract subsecond part.
That's the function I was thinking first and turned back to this hideous manipulation of strings.

Regards
Michel
- Re: difference between two timestamps [message #236263 is a reply to message #236228] Wed, 09 May 2007 07:59 Go to previous messageGo to next message
soujanya_srk
Messages: 111
Registered: November 2006
Location: HYDERABAD
Senior Member
thanks a lot for both of u, now i am getting a lot of
zeroes in the result...so does it mean the input itself is in
milliseconds? in that case is there a way to print the result
in decimals...i mean assuming the time difference is in
milliseconds ...........
- Re: difference between two timestamps [message #236269 is a reply to message #236251] Wed, 09 May 2007 08:09 Go to previous messageGo to next message
Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
What do you mean?
You asked a result in milliseconds so we gave you in milliseconds and of course there are more figures.
Post an example of what you want.

Regards
Michel
- Re: difference between two timestamps [message #236277 is a reply to message #236251] Wed, 09 May 2007 08:22 Go to previous messageGo to next message
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Quote:

Unfortunatly EXTRACT can't extract subsecond part.



That's what I believed, too, until I researched the question just now. I have found that extract actually returns the seconds with fractions.

E.g. when I run my script the 5.300331 second difference between the timestamps is returned as 5.300331 seconds with extract.

TS1 : 15:17:43 836901 / TS2: 15:17:49 137232
Difference as Interval : +00 00:00:05.300331
Difference as MS     : 5300.331


That's on Version 9.2.0.8.
- Re: difference between two timestamps [message #236278 is a reply to message #236277] Wed, 09 May 2007 08:27 Go to previous messageGo to next message
Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Doh! I forgot that. Embarassed
And you know what, I checked in my scripts and found that I used it!!!

Regards
Michel
- Re: difference between two timestamps [message #236286 is a reply to message #236228] Wed, 09 May 2007 08:39 Go to previous messageGo to next message
soujanya_srk
Messages: 111
Registered: November 2006
Location: HYDERABAD
Senior Member
i mean, instead of 0, can i get the result like 0.351 etc
- Re: difference between two timestamps [message #236288 is a reply to message #236286] Wed, 09 May 2007 08:41 Go to previous message
Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Well, remove "*1000" part in Thomas' code.

Regards
Michel
Previous Topic: Doubt in the query regarding deletion of duplicate records from the table
Next Topic: Help with tough query
Goto Forum:
  


Current Time: Thu Jun 12 16:33:31 CDT 2025