| How to change Time format [message #374189] |
Wed, 30 May 2001 00:44  |
Thirumalai and Prakash
Messages: 23
Registered: March 2001
|
Junior Member |
|
|
I need to find, how much time my query is getting executed. So i am using SET TIMING ON to get the time taken. But the format iam Getting is in REAL. I want the output in the following format HH:MI:SS.
Pls revert back
Thanx in Advance
ACP Prakash/Thirumalai
|
|
|
|
| Re: How to change Time format [message #374212 is a reply to message #374189] |
Wed, 30 May 2001 11:32  |
Andrew again...
Messages: 270
Registered: July 2000
|
Senior Member |
|
|
In all the versions of sqlplus I've worked with, the timing is reported in millisec in windows, but in h:m:s.tt in Unix (down to 1/100 sec). You can't change that.
If you get this value into a variable, you can concert it to a fraction of a day and then use to_char to format it into h:m:s (no resolution lower than seconds).
CREATE OR REPLACE FUNCTION to_hms (v_days IN number)
RETURN varchar2
IS
v_retval varchar2(15);
BEGIN
v_retval := TO_CHAR (TRUNC (v_days)) ||
TO_CHAR (TRUNC (SYSDATE) + MOD (v_days, 1), ' HH24:MI:SS');
RETURN v_retval;
END to_hms;
/
1Min, 03 Sec is 63 sec.
63 sec = 63/(60*24) days.
select to_hms(63/(60*24)) from dual;
0 01:03:00
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
