A simple subtraction the those values using this query yields:
SQL> select runnumber, starttime, endtime, (endtime-starttime) TotalMinutes0 from run_log; RUN STARTTIME ENDTIME TOTALMINUTES0 ---- -------------------- -------------------- ------------- 3 24-APR-15 12:07:49 24-APR-15 12:58:37 0.03527777778 2 24-APR-15 11:00:47 24-APR-15 11:55:11 0.03777777778 1 24-APR-15 08:00:13 24-APR-15 08:52:39 0.03641203704
So, I convert the difference to minutes by multiplying it by 1440 (24 Hours X 60 Minutes) using this below query:
SQL> select runnumber, starttime, endtime, (endtime-starttime) TotalMinutes0,
to_char((endtime-starttime)*1440,'99.99') TotalMinutes1 -- OR (24 Hours X 60 Minutes)
from run_log order by starttime desc ;
RUN STARTTIME ENDTIME TOTALMINUTES0 TOTALMINUTES1
---- -------------------- -------------------- ------------- -------------
3 24-APR-15 12:07:49 24-APR-15 12:58:37 0.03527777778 50.80
2 24-APR-15 11:00:47 24-APR-15 11:55:11 0.03777777778 54.40
1 24-APR-15 08:00:13 24-APR-15 08:52:39 0.03641203704 52.43
That's great progress, but now the seconds part is a decimal fraction instead of seconds. This necessitates the wielding of the Oracle "numtodsinterval" built-in function using this below query:
SQL> select runnumber, starttime, endtime, (endtime-starttime) TotalMinutes0,
to_char((endtime-starttime)*1440,'99.99') TotalMinutes1,
numtodsinterval((endtime-starttime),'day') TotalMinutes2
from run_log order by starttime desc ;
RUN STARTTIME ENDTIME TOTALMINUTES0 TOTALMINUTES1 TOTALMINUTES2
---- -------------------- -------------------- ------------- ------------- -------------
3 24-APR-15 12:07:49 24-APR-15 12:58:37 0.03527777778 50.80 0 0:50:48.0
2 24-APR-15 11:00:47 24-APR-15 11:55:11 0.03777777778 54.40 0 0:54:24.0
1 24-APR-15 08:00:13 24-APR-15 08:52:39 0.03641203704 52.43 0 0:52:26.0
The output is good and we now only need to trim it a bit as in the below query:
SQL> select runnumber, starttime, endtime, (endtime-starttime) TotalMinutes0,
to_char((endtime-starttime)*1440,'99.99') TotalMinutes1,
numtodsinterval((endtime-starttime),'day') TotalMinutes2,
substr(numtodsinterval((endtime-starttime),'day'), 4, 8) TotalMinutes3 -- Ignore hours
from run_log order by starttime desc ;
RUN STARTTIME ENDTIME TOTALMINUTES0 TOTALMINUTES1 TOTALMINUTES2 TOTALMINUTES3
---- -------------------- -------------------- ------------- ------------- ------------- -------------
3 24-APR-15 12:07:49 24-APR-15 12:58:37 0.03527777778 50.80 0 0:50:48.0 0000000
2 24-APR-15 11:00:47 24-APR-15 11:55:11 0.03777777778 54.40 0 0:54:24.0 0000000
1 24-APR-15 08:00:13 24-APR-15 08:52:39 0.03641203704 52.43 0 0:52:26.0 0000000
Now that's really very strange - the trimming substring does not get me the expected value - wonder what's going on? To check that, we use "to_char" function to see what is happening during the conversion to string as in the below query:
SQL> select runnumber, starttime, endtime, (endtime-starttime) TotalMinutes0,
to_char((endtime-starttime)*1440,'99.99') TotalMinutes1,
numtodsinterval((endtime-starttime),'day') TotalMinutes2,
substr(numtodsinterval((endtime-starttime),'day'), 4, 8) TotalMinutes3, -- Ignore hours
to_char(numtodsinterval((endtime-starttime),'day')) TotalMinutes4
from run_log order by starttime desc ;
RUN STARTTIME ENDTIME TOTALMINUTES0 TOTALMINUTES1 TOTALMINUTES2 TOTALMINUTES3 TOTALMINUTES4
---- -------------------- -------------------- ------------- ------------- ------------- ------------- ------------------------------
3 24-APR-15 12:07:49 24-APR-15 12:58:37 0.03527777778 50.80 0 0:50:48.0 0000000 +000000000 00:50:48.000000000
2 24-APR-15 11:00:47 24-APR-15 11:55:11 0.03777777778 54.40 0 0:54:24.0 0000000 +000000000 00:54:24.000000000
1 24-APR-15 08:00:13 24-APR-15 08:52:39 0.03641203704 52.43 0 0:52:26.0 0000000 +000000000 00:52:26.000000000
Ahh ha! Substring converts the given value to string before getting us a part of it and the conversion to string gives us a totally different & larger string! So now we use trim the result as in the below query to get the executed final result:
SQL> select runnumber, starttime, endtime, (endtime-starttime) TotalMinutes0,
to_char((endtime-starttime)*1440,'99.99') TotalMinutes1,
numtodsinterval((endtime-starttime),'day') TotalMinutes2,
substr(numtodsinterval((endtime-starttime),'day'), 4, 8) TotalMinutes3, -- Ignore hours
to_char(numtodsinterval((endtime-starttime),'day')) TotalMinutes4,
substr(numtodsinterval((endtime-starttime),'day'), 15, 8) TotalMinutes5 -- Ignore hours
from run_log order by starttime desc ;
RUN STARTTIME ENDTIME TOTALMINUTES0 TOTALMINUTES1 TOTALMINUTES2 TOTALMINUTES3 TOTALMINUTES4 TOTALMINUTES5
---- -------------------- -------------------- ------------- ------------- ------------- ------------- ------------------------------ -------------
3 24-APR-15 12:07:49 24-APR-15 12:58:37 0.03527777778 50.80 0 0:50:48.0 0000000 +000000000 00:50:48.000000000 50:48.00
2 24-APR-15 11:00:47 24-APR-15 11:55:11 0.03777777778 54.40 0 0:54:24.0 0000000 +000000000 00:54:24.000000000 54:24.00
1 24-APR-15 08:00:13 24-APR-15 08:52:39 0.03641203704 52.43 0 0:52:26.0 0000000 +000000000 00:52:26.000000000 52:26.00
Finally! The time difference is displayed in the format HH24:MI:SS.NNN
So after five iterations, I finalize that this exercise requires using the Oracle built-in function "numtodsinterval" which takes a number of a given interval unit and converts it to an INTERVAL DAY TO SECOND literal. The interval value indicates the unit of the specified number and the case-insensitive valid values are:
- 'DAY'
- 'HOUR'
- 'MINUTE'
- 'SECOND'
In our case, the number in question is the simple difference of the two time values and its' unit is a day so the interval value is "Day".
No comments:
Post a Comment