Oracle SQL Convert number field to hh:mm-CodePudding

I have looked at other similar articles but non are giving me the correct outcome. I have a field called 'Duration' with numbers in minutes stored as an integer. I need to convert that to hh:mm so:

Duration	hh:mm
120	02:00
545	09:08
3600	60:00

I've experimented with to_char, to_date, and a few others but not getting anywhere.

CodePudding user response：

You can try using:

NUMTODSINTERVAL function, that will transform your integer to seconds
TO_CHAR function, that will format your seconds to your required time frame

SELECT Duration, 
       TO_CHAR(TIME'0:0:0' NUMTODSINTERVAL(Duration,'second'),'hh24:mi:ss')
FROM tab

Check the demo here.

CodePudding user response：

Divide the duration (seconds) by 60. The quotient is the minutes, the remainder is the seconds.

select
  duration,
  to_char(trunc(duration / 60), 'FM00') ||
  ':' ||
  to_char(mod(duration, 60), 'FM00') as time
from mytable;