Thanks for the question, Manohar.

Asked: June 28, 2017 - 8:51 am UTC

Last updated: November 30, 2017 - 1:59 pm UTC

Version: 11g Express

Viewed 10K+ times! This question is

You Asked

I was trying to verify how true would be the values returned by the function DBMS_UTILITY.GET_TIME using the below code.

DECLARE
  curr_time1 DATE := SYSDATE;
  curr_time2 NUMBER := dbms_utility.get_time;
BEGIN
  LOOP
   EXIT WHEN SYSDATE >= curr_time1 + (5/(24*60*60));-- exit loop after 5 seconds
  END LOOP;
  dbms_output.put_Line ('Elapsed Time = '||(dbms_utility.get_time - curr_time2)/100);
END;
/

And I got 3 different values, as below, after executing this code 3 times repeatedly.

+++++++++++++
PL/SQL procedure successfully completed.

Elapsed Time = 4.17

PL/SQL procedure successfully completed.

Elapsed Time = 4.36

PL/SQL procedure successfully completed.

Elapsed Time = 4.02
+++++++++++++

Apparently after completing the loop of 5 seconds, I was expecting the elapsed time to be closer to 5 seconds, or at least greater than 5 secords, but I am surprised to see the values not to be so. Am I missing something in this?

and Chris said...

What you're observing here is due to the granularity of dates. These only report times to the nearest second.

So if you get the initial time at 00:00:00.999999, your variable has the (time) value of 00:00:00. Your loop will exit when the time hits 00:00:05. Thus it's only 4.00000...1s before you hit this!

Switch to timestamps which have up to 9 fractional seconds of precision and you should see it report 5s consistently:

declare
  curr_time1   timestamp with time zone := systimestamp;
  curr_time2   number := dbms_utility.get_time;
begin

  loop
    exit when current_timestamp >= curr_time1 + interval '5' second;
  end loop;

  dbms_output.put_line( 'Elapsed Time = '
   || ( dbms_utility.get_time - curr_time2 ) / 100 );

end;
/
Elapsed Time = 5

Rating

(3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks

Manohar Mishra, July 04, 2017 - 10:19 am UTC

Thanks for updating me with this useful information.

Further question

Manohar Mishra, November 30, 2017 - 11:23 am UTC

I have a further question to this.
How can I parameterize the value 5 (second) in the below statement ?

exit when current_timestamp >= curr_time1 + interval '5' second;

November 30, 2017 - 1:59 pm UTC

Either use a variable of type of interval day to second or use numtodsinterval to convert:

declare
  curr_time1   timestamp with time zone := systimestamp;
  curr_time2   number := dbms_utility.get_time;
  sec          pls_integer := 5;
  sec_int      interval day to second;
begin

  sec_int := '0 00:00:05';

  loop
    exit when current_timestamp >= curr_time1 + sec_int;
-- OR   exit when current_timestamp >= curr_time1 + numtodsinterval(sec, 'second');
  end loop;

  dbms_output.put_line( 'Elapsed Time = '
   || ( dbms_utility.get_time - curr_time2 ) / 100 );

end;
/

Great and Handy

Manohar Mishra, December 01, 2017 - 5:05 am UTC

Thanks for this great and handy information.

Just want to share the below snippet which I tried to verify based upon your info.

DECLARE
Curr_Time1 TIMESTAMP WITH TIME ZONE := Systimestamp;
Sec_Int INTERVAL DAY TO SECOND := '0 00:00:30';
Min_Int INTERVAL DAY TO SECOND := '0 00:30:00';
Hour_Int INTERVAL DAY TO SECOND := '0 01:00:00';
Curr_Time2 TIMESTAMP WITH TIME ZONE := Systimestamp + Min_Int;
BEGIN
Dbms_Output.Put_Line ('Current Date and time is ' || Curr_Time1);
-- dbms_output.put_line('Incremented Date and time is '||curr_time2); -- this works
-- dbms_output.put_line('Incremented Date and time is '||(curr_time1 + min_int)); -- this also works
Dbms_Output.Put_Line ('Incremented Date and time is ' || (Curr_Time1 + Hour_Int) ); -- this also works
-- dbms_output.put_line('Incremented Date and time is '||(systimestamp + numtodsinterval('30', 'minute')));
END;

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

Thanks

Further question

Great and Handy

More to Explore

PL/SQL demos

PL/SQL docs

How true is the DBMS_UTILITY.GET_TIME function

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

Thanks

Further question

Great and Handy

More to Explore

PL/SQL demos

PL/SQL docs