Convert milliseconds to DD:HH:MM:SS

EricPSU05 · Apr 1, 2016 11:48AM

I have a fact that is displaying the number of milliseconds between two dates that is calculated by a database view. I want to display this fact as "7 Days 14 Hours 45 Minutes 12 Seconds". What formula should I use to convert milliseconds to that output? Thanks!

Ebin · Apr 1, 2016 3:41PM

Hi,

Convert millisecond to second and then use custom strings in OBIEE to convert seconds into dd hh:mm:ss format. Use the Custom Numeric Format as [duration(sec)][opt:dd]:hh:mm:ss

https://docs.oracle.com/cd/E28280_01/bi.1111/e10544/format.htm#BIEUG11528

Thomas Dodds · Apr 1, 2016 3:41PM

Another approach ....

SELECT TO_CHAR(TRUNC((((86400*x)/60)/60)/24))

|| 'days '

|| TO_CHAR(TRUNC(((86400*x)/60)/60)-24*(TRUNC((((86400*x)/60)/60)/24)), 'FM00')

|| 'hrs '

|| TO_CHAR(TRUNC((86400*x)/60)-60*(TRUNC(((86400*x)/60)/60)), 'FM00')

|| 'mins '

|| TO_CHAR(TRUNC(86400*x)-60*(TRUNC((86400*x)/60)), 'FM00')

|| 'secs' "Duration"

FROM

(SELECT (sysdate - (sysdate-1)) x FROM dual t

);

^ gets the millisecs and then works them out to the same bits you need ... so use BOLD bit to be the physical expression in the logical column. If you want individual columns then just cut up the BOLD bit - same approach use a logical column and edit it's physical mapping expression.

EricPSU05 · Apr 4, 2016 11:53AM

This looks like what I need, but unfortunately I am still using 10g. Is the optional available in 10g? I can't seem to get it to work. Thanks!

Ebin · Apr 5, 2016 9:56AM

Hi Eric,

I don't remember using this in 10g and also don't have a 10g application in front of me.

Thomas Dodds · Apr 5, 2016 10:51AM

Modeling it makes it reusable and works in both 10g/11g ...

MFA for Oracle Community

Oracle Analytics Cloud and Server

Categories

Convert milliseconds to DD:HH:MM:SS

Answers