I have a table in my database that has a NUMBER(12) column value representing an amount of time in seconds. For a report I’m building, I’d like to format this in an hours : minutes : seconds style.
Initially, I thought I could achieve this using the Oracle TO_DATE and TO_CHAR functions, but I couldn’t, as some of the seconds values represent an hours value of over 24 hours, and any amount of time greater than 24 hours cannot be rendered in an Oracle hh:mm:ss format.
(Times over 24 hours can only be displayed as a number of days).
So, I built this bit of SQL and created a measure object with a CHARACTER type.
to_char(trunc(sum(<seconds_column>)/3600), 'FM999999990') || ':' || to_char(trunc(mod(sum((<seconds_column>),3600)/60), 'FM00') || ':' || to_char(mod(sum((<seconds_column>),60), 'FM00')
This now renders a numeric value as a time in hours, minutes and seconds. It took me a while, and there doesn’t seem to be anything along these lines on the forum anywhere, so I thought I’d share it. Hopefully, it may be of use to someone else.
Darren.
Darren Griffin (BOB member since 2002-07-10)