We have several objects in the database that are returning a duration value in SECONDS. Is there a way to create a function that would pass this SECONDS value into an HH:MM:SS to be displayed at the report level.
Thank you for the formula but this formula is to be applied at the report level which we already have. I need to define an object in the universe (DB2 database) that would do this conversion.
Why not taking the report level formula as a base and rewrite it using SQL functions that are supported by your DB? Then you should get a formula that could be used in a universe object definition.
OK, I have define an object at the universe level that will convert the seconds into HH:MM:SS…
time(‘00:00:00’) +sum(VP.TCA11E_CALL_DET.QUEUE_TIME) second
The problem is that this will work if within 24 hours. Everything more than 24 hours will restart at 00:00:00. If I have 86402 seconds it will convert 00:00:02
The mod() function will return the remainder of a division. So if you mod() with 86400 it will return the number of seconds, less the number of full days. As far as I know, most databases have this function.
I am not sure what the MOD function will do…
If I use the following object in the universe:
time(‘00:00:00’) +sum(VP.TCA11E_CALL_DET.QUEUE_TIME) second
This wil returm 02:38:29 if VP.TCA11E_CALL_DET.QUEUE_TIME = 9509 seconds
And it will return 00:00:29 if VP.TCA11E_CALL_DET.QUEUE_TIME = 86429 seconds instead of 24:00:29
Maybe that solution might work for you, it involves creating a custom function. It also mentions that timestamps do not go beyond 24 hours, for that you need an interval instead.