0

How can i Select my Runtime column (defaults to seconds) to display time in HH:Mi format such as 106680 seconds = 29:38. By default HH24:Mi wraps the time beyond 24 hours.

kkoolpatz
  • 156
  • 11
  • 2
    You'll find answer here: [Seconds to hours:minutes:seconds format](http://stackoverflow.com/questions/11003918/oracle-convert-seconds-to-hoursminutesseconds) – Przemyslaw Kruglej Nov 11 '13 at 18:52
  • Are you sure it's not just an improper NLS setting? (Sql developer tools-preferences-database-NLS (DD-MON-RR HH24:MI:SS) is default I think. – xQbert Nov 11 '13 at 18:53
  • No, it was not a format problem. I was able to change my format however i wanted to, but Oracle just does not have a HH:Mi format that goes beyond 24 hours. – kkoolpatz Nov 13 '13 at 00:25
  • Thank you @PrzemyslawKruglej Your link helped. :) – kkoolpatz Nov 13 '13 at 00:37

1 Answers1

1

How about something like this:

Select to_char(TRUNC(runtimeCol/3600),'09') || ':' || to_char(TRUNC(MOD(runtimeCol,3600)/60),'09')  from yourTable
rkh
  • 841
  • 12
  • 29
  • @kkoolpatz did this work for you? If so please indicate accordingly! – rkh Nov 12 '13 at 16:36
  • This worked very well, except there is a space after the colon. But that was okay.Thank you very much. `TO_CHAR ------- 29: 38 30: 00 25: 47 33: 00 32: 39` – kkoolpatz Nov 13 '13 at 00:22
  • 1
    I was able to get around the space issue by using FM00 instead of 09 `to_char(TRUNC(MOD((ENDTIME-STARTIME),3600)/60),'FM00')` – kkoolpatz Nov 13 '13 at 00:35