I have a table which is storing a number of hours as a number (this I cannot change). This can be any decimal number. I am converting that into hours and minutes for display purposes, however I would like to round UP the minutes part.
select extract (hour from numtodsinterval (8.333, 'HOUR')) hours,
extract (minute from numtodsinterval (8.333, 'HOUR')) mins,
numtodsinterval (8.333, 'HOUR') intv
from dual;
| hours | mins | intv
|
|---|
| 8 | 19 | +00 08:19:58.800000 |
What I want to do is round UP the number of minutes in the interval - so I have 8 hours, 19 minutes, 58 seconds (8.333 hours) - I want to display that as 8 hours 20 minutes.
I've tried the obvious of using ceil but that can't be applied to an interval.
I know I can do the maths manually :
with x as
(select 8.333 i from dual)
Select floor(i) hours, round(mod(i,1) * 60) mins From x;
but I wanted to check this couldn't be done with intervals (more from an academic perspective) before going down that route. I also prefer the Interval syntax as I find it more obvious what is going on from a readability point of view.