We have a bunch of “durations” stored in Oracle as
FLOAT data types (as day intervals, meaning they are an interval that includes days).
This makes sense. from a database storage perspective. However, I didn’t know how to get those values into the more human-comprehensible hh:mm:ss format.
An Oracle function called
NUMTODSINTERVAL is exactly what we need here. As the name suggests, it converts a number to a DateStamp interval (which is to say, interprets a number as a time span). We can tell it the type of time span (days in this case), and then extract the appropriate time values.
NUMTODSINTERVAL ([yourvalue], '[typeofinterval]')
NUMTODSINTERVAL (tablename.durationfield, 'DAY')
The above example takes my field named
durationfiel’ and tells oracle it’s a DAY interval. From here, we use the
EXTRACT function to get what we want.
Gets the number of hours (and converts all day values to 24 hours each):
EXTRACT(DAY FROM NUMTODSINTERVAL(TABLENAME.DURATIONFIELD, 'DAY')) * 24 + EXTRACT(HOUR FROM NUMTODSINTERVAL( TABLENAME.DURATIONFIELD , 'DAY')) AS DURATIONHOURS
Gets the minutes in the same fashion:
EXTRACT(MINUTE FROM NUMTODSINTERVAL( TABLENAME.DURATIONFIELD , 'DAY')) AS DURATIONMINUTES
Gets the seconds in the same fashion:
EXTRACT(SECOND FROM NUMTODSINTERVAL( TABLENAME.DURATIONFIELD, 'DAY')) AS DURATIONSECONDS